SQL:如何用一个sql统计出全校男生个数、女生个数以及总人数

SQL:如何用一个sql统计出全校男生个数、女生个数以及总人数

 

 本文为转载: https://blog.csdn.net/wangjinsu7/article/details/52257150

情景：统计全校的男生、女生、以及总人数 
表结构如下：

table   : students          
coulmns :
studentID char(40) NOT NULL
studentNAME char(40) NULL
sexID char(20) NULL (1:boy;2:girl)

•  

方式一：分别查询

SELECT COUNT(1)AS allcount FROM students ;
SELECT COUNT(1)AS boycount FROM students WHERE sexID = 1;
SELECT COUNT(1)AS girlcount FROM students WHERE sexID = 2;

•  

方式二：left join

SELECT a.allcount,
       b.boycount,
       c.girlcount
FROM
  (SELECT COUNT(1)AS allcount
   FROM students)AS a
LEFT JOIN
  (SELECT COUNT(1)AS boycount
   FROM students
   WHERE sexID = 1)b ON 1=1
LEFT JOIN
  (SELECT COUNT(1)AS girlcount
   FROM students
   WHERE sexID = 2)c ON 1=1

•  

方式三：使用dual

SELECT
  (SELECT COUNT(1)AS allcount
   FROM students)AS a,
  (SELECT COUNT(1)AS boycount
   FROM students
   WHERE sexID = 1)b,
  (SELECT COUNT(1)AS girlcount
   FROM students
   WHERE sexID = 2)c
FROM DUAL

•  

方式四：使用group by

SELECT COUNT(1)
FROM students
GROUP BY sexID

•  

方式五：使用case when(mysql) 或 decode(oracle)

-- mysql:
SELECT COUNT(1)AS allcount,
       SUM(CASE WHEN sexID=1 THEN 1 ELSE 0 END)AS boycount,
       SUM(CASE WHEN sexID=1 THEN 0 ELSE 1 END)AS girlcount
FROM students

-- oracle:
SELECT COUNT(1)AS allcount,
       SUM(DECODE(sexID,1,1,0))AS boycount,
       SUM(DECODE(sexID,2,1,0))AS girlcount
FROM students