通过跳过一行进行查询组 - Query group by skipping a row

Query group by skipping a row

本文关键字：查询一行 | 更新日期: 2023-09-27

我有一张桌子

students[std_id, name, class, gender,etc]
select class,gender,count(*) as total_students 
from students 
group by class,gender

其输出如下

 1st | male   | 23   
 1st | female | 11   
 2nd | male   | 17   
 2nd | female | 0

/最后一行未显示，因为二班有0名女生

如何使其如上显示，total_sudents=0而不是跳过记录。

您可以通过为每个性别编写一个查询，然后将它们联合起来来实现：

select class, 'male' as gender, 
    count(case when gender = 'male' then 1 end) as total_students 
from students 
group by class
union all
select class, 'female' as gender, 
    count(case when gender = 'female' then 1 end) as total_students 
from students 
group by class

或者，您可以执行以下操作：

select class, 
    count(case when gender = 'male' then 1 end) as total_male_students,
    count(case when gender = 'female' then 1 end) as total_female_students  
from students 
group by class

使用此解决方案：

SELECT    a.class,
          a.gender,
          COUNT(b.class) AS total_students
FROM      (
          SELECT     a.class, 
                     b.gender
          FROM       students a
          CROSS JOIN (
                     SELECT 'male' AS gender UNION ALL 
                     SELECT 'female'
                     ) b
          GROUP BY   a.class, 
                     b.gender
          ) a
LEFT JOIN students b ON a.class = b.class AND 
                        a.gender = b.gender
GROUP BY  a.class,
          a.gender