有一省份表，现想按省份统计出每个省份有多少个学生，多少个老师，总分是多少
弄了半天没弄出来，求救一下各位
测试数据其期望结果见下
- SQL code
create table province( id int, name nvarchar(50))insert into provinceselect 1, '北京'union allselect 2, '上海'union allselect 3, '广东'create table student( id int, name nvarchar(50), provinceId int)insert into studentselect 1, '张三', 1union allselect 2, '李四', 1union allselect 3, '王二', 2union allselect 4, '麻子', 2union allselect 5, '赵五', 2create table teacher( id int, name nvarchar(50), provinceId int)insert into teacherselect 1, '语文老师', 1union allselect 2, '数学老师', 2union allselect 3, '英语老师', 3union allselect 4, '地理 老师', 3create table score( studentId int, score int)insert into scoreselect 1, 80union allselect 2, 50union allselect 3, 200--期望得到--id name student teacher totalScore--1 北京 2 1 130--2 上海 3 1 200--3 广东 0 2 0
------解决方案--------------------
create table province
(
id int,
name nvarchar(50)
)
insert into province
select 1, '北京'
union all
select 2, '上海'
union all
select 3, '广东'
create table student
(
id int,
name nvarchar(50),
provinceId int
)
insert into student
select 1, '张三', 1
union all
select 2, '李四', 1
union all
select 3, '王二', 2
union all
select 4, '麻子', 2
union all
select 5, '赵五', 2
create table teacher
(
id int,
name nvarchar(50),
provinceId int
)
insert into teacher
select 1, '语文老师', 1
union all
select 2, '数学老师', 2
union all
select 3, '英语老师', 3
union all
select 4, '地理 老师', 3
create table score
(
studentId int,
score int
)
insert into score
select 1, 80
union all
select 2, 50
union all
select 3, 200
--期望得到
--id name student teacher totalScore
--1 北京 2 1 130
--2 上海 3 1 200
--3 广东 0 2 0
select province.id,province.name,
count(student.name) as student,
COUNT(teacher.name) as teacher,
sum(score)totalscore
from province
left join student on province.id=student.provinceId
left join teacher on province.id=teacher.provinceId
left join score on student.id=score.studentId
group by province.id,province.name
order by province.id asc
/*
--结果：
id name student teacher totalscore
1 北京 2 2 130
2 上海 3 3 200
3 广东 0 2 NULL
*/
------解决方案--------------------
- SQL code
select a.id,a.name,count(distinct b.name) as student,count(distinct c.name) as teacher,isnull(sum(score),0) as totalScorefrom province aleft join student b on a.id=b.provinceidleft join teacher c on a.id=c.provinceidleft join score d on b.id=d.studentidgroup by a.id,a.nameorder by a.id/**id name student teacher totalScore----------- -------------------------------------------------- ----------- ----------- -----------1 北京 2 1 1302 上海 3 1 2003 广东 0 2 0(3 行受影响)**/
------解决方案--------------------
- SQL code
---测试数据 创建表provincecreate table province( id int, name nvarchar(50))---向province插入记录insert into provinceselect 1, '北京'union allselect 2, '上海'union allselect 3, '广东'----创建表studentcreate table student( id int, name nvarchar(50), provinceId int)---student表插入记录insert into studentselect 1, '张三', 1union allselect 2, '李四', 1union allselect 3, '王二', 2union allselect 4, '麻子', 2union allselect 5, '赵五', 2create table teacher( id int, name nvarchar(50), provinceId int)insert into teacherselect 1, '语文老师', 1union allselect 2, '数学老师', 2union allselect 3, '英语老师', 3union allselect 4, '地理 老师', 3create table score( studentId int, score int)insert into scoreselect 1, 80union allselect 2, 50union allselect 3, 200----查询SQL codeselect a.id,a.name,count(distinct b.name) as student,count(distinct c.name) as teacher,isnull(sum(score),0) as totalScorefrom province aleft join student b on a.id=b.provinceidleft join teacher c on a.id=c.provinceidleft join score d on b.id=d.studentidgroup by a.id,a.nameorder by a.id/*id name student teacher totalScore----------- -------------------------------------------------- ----------- ----------- -----------1 北京 2 1 1302 上海 3 1 2003 广东 0 2 0(3 行受影响)*/