求一个省份统计sql, group by 相关

[wdwwtzy]

有一省份表，现想按省份统计出每个省份有多少个学生，多少个老师，总分是多少
弄了半天没弄出来，求救一下各位

测试数据其期望结果见下

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

[Rotel-刘志东]

搞清楚表间的关系，连接查询就可以实现的。

[开着拖拉机泡妞]

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

*/

[wdwwtzy]

呃，楼上的结果不对啊...

[百年树人]

select a.id,a.name,count(distinct b.name) as student,
count(distinct c.name) as teacher,isnull(sum(score),0) as totalScore
from province a
left join student b on a.id=b.provinceid
left join teacher c on a.id=c.provinceid
left join score d on b.id=d.studentid
group by a.id,a.name
order by a.id
/**
id          name                                               student     teacher     totalScore
----------- -------------------------------------------------- ----------- ----------- -----------
1           北京                                                 2           1           130
2           上海                                                 3           1           200
3           广东                                                 0           2           0

(3 行受影响)
**/

[Rotel-刘志东]

---测试数据 创建表province
create table province
(
    id int,
    name nvarchar(50)
)
---向province插入记录
insert into province
select 1, '北京'
union all
select 2, '上海'
union all
select 3, '广东'
----创建表student
create table student
(
    id int,
    name nvarchar(50),
    provinceId int
)
---student表插入记录
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


----查询
SQL codeselect a.id,a.name,count(distinct b.name) as student,
count(distinct c.name) as teacher,isnull(sum(score),0) as totalScore
from province a
left join student b on a.id=b.provinceid
left join teacher c on a.id=c.provinceid
left join score d on b.id=d.studentid
group by a.id,a.name
order by a.id
/*
id          name                                               student     teacher     totalScore
----------- -------------------------------------------------- ----------- ----------- -----------
1           北京                                                 2           1           130
2           上海                                                 3           1           200
3           广东                                                 0           2           0

(3 行受影响)
*/

[开着拖拉机泡妞]

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(distinct student.name) as student,
count(distinct teacher.name) as teacher,
isnull(sum(score),0) as 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 1 130
2 上海 3 1 200
3 广东 0 2 0L

*/

[wdwwtzy]

呃，多谢，原来可以count里加distinct啊....郁闷....

[victor_yang]

select a.*,student=isnull(student,0),teacher=isnull(teacher,0),totalscore=isnull(totalScore,0)
from province a
left join (select provinceid,student=count(id) from student group by provinceid) b
on a.id = b.provinceid
left join (select provinceid,teacher=count(id) from teacher group by provinceid) c
on a.id = c.provinceid
left join (select s2.provinceid,totalScore=sum(s1.score) from score s1 left join student s2 on s1.studentId=s2.id group by s2.provinceid) d
on a.id = d.provinceid

id name student teacher totalscore
----------- -------------------------------------------------- ----------- ----------- -----------
1 北京 2 1 130
2 上海 3 1 200
3 广东 0 2 0

（所影响的行数为 3 行）

[wdwwtzy]

另外，我在想可不可以用子查询的方式来做呢？谁教教我...连接查询有各种情况出现...

[勿勿]

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   a.id,a.name,count(distinct b.name) as student,
count(distinct c.name) as teacher,isnull(sum(score),0) as totalScore
from province a
left join student b on a.id=b.provinceid
left join teacher c on a.id=c.provinceid
left join score d on b.id=d.studentid
group by a.id,a.name
order by a.id


id          name                                               student     teacher     totalScore
----------- -------------------------------------------------- ----------- ----------- -----------
1           北京                                                 2           1           130
2           上海                                                 3           1           200
3           广东                                                 0           2           0
警告: 聚合或其他 SET 操作消除了 Null 值。

(3 行受影响)

[victor_yang]

我已经写了个子查询的给你了，看看吧