您的位置: 首页  >  IT文章  >  sql 省份

sql 省份

分类: IT文章 • 2024-12-20 20:48:06
场景：省份统计sql, group by 相关

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

测试数据其期望结果见下
SQL code

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




------解决方案--------------------

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 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 行受影响)
**/

------解决方案--------------------
SQL code
---测试数据 创建表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

*/


------解决方案--------------------
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 行）

------解决方案--------------------
SQL code
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 行受影响)

相关推荐