你参考一下~~ 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
非常感谢!! 已经解决我用的是MySql 如果用的是MySql的话 ISNULL改成 IFNULL
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 行受影响)
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
已经解决我用的是MySql 如果用的是MySql的话 ISNULL改成 IFNULL
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 行受影响)
只要把sex值是1的话就表示男生 只要把sex值求和就得到男生数了
谢谢4楼