表1 tb_school
学校编号 XXID 学校名称 XXNM
表2 tb_student
性别 男生1 女生0
用SQL语句查询 结果显示为以下形式
学校编号 | 学校名称 | 总人数 | 男生数 | 女生数
我已经查询出前三个 不知道后面两个男生数 和 女生数 怎么查询显示在一个结果里
这是我已经写出来的
------解决思路----------------------
你参考一下~~
SELECT
tb_sch.XXID[学校编号]
,tb_sch.XXNM[学校名称]
,COUNT(tb_stu.ID)[总人数]
,ISNULL(SUM(CASE tb_stu.sex WHEN 1 THEN 1 END),0)[男生数]
,ISNULL(SUM(CASE tb_stu.sex WHEN 0 THEN 1 END),0)[女生数]
FROM
tb_school tb_sch
LEFT JOIN tb_student tb_stu ON tb_sch.XXID = tb_stu.XXID
GROUP BY
tb_sch.XXID
,tb_sch.XXNM
------解决思路----------------------
with cte as
(select 1 as id,1 as xxid,'山大' as xxnm,'山大二货'as name,1 as sex union all
select 2 as id,2 as xxid,'北大' as xxnm,'北大学霸'as name,0 as sex union all
select 3 as id,1 as xxid,'山大' as xxnm,'哈哈哈'as name,1 as sex union all
select 4 as id,3 as xxid,'清华' as xxnm,'清华高材生'as name,1 as sex union all
select 5 as id,3 as xxid,'清华' as xxnm,'清华大美女'as name,0 as sex union all
select 6 as id,4 as xxid,'人大' as xxnm,'人大啥东西'as name,1 as sex union all
select 7 as id,4 as xxid,'人大' as xxnm,'还是人大'as name,0 as sex union all
select 8 as id,2 as xxid,'北大' as xxnm,'北大美女'as name,0 as sex union all
select 9 as id,1 as xxid,'山大' as xxnm,'猴子'as name,1 as sex )
select xxid,xxnm,COUNT(xxid)as 总人数,
SUM(sex) as 男生数, COUNT(xxid)-SUM(sex) as 女生数 from cte
group by xxid,xxnm
order by xxid
--结果
xxid xxnm 总人数 男生数 女生数
----------- ---- ----------- ----------- -----------
1 山大 3 3 0
2 北大 2 0 2
3 清华 2 1 1
4 人大 2 1 1
(4 行受影响)