情景:统计全校的男生、女生、以及总人数
表结构如下:
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
总结:其中方式一、二、三都是执行了三次查询,方式四直接使用group by 但是返回的是一列数据(纵向),而且没有总人数的统计,方式五巧妙地将性别转成数字,并用sum方法分别计算出学生男女个数,返回的结果是三列数据(横向)。
(实际工作中,还要考虑到字段值为空的情况)