日期:2014-05-17 浏览次数:20724 次
WITH T AS (
SELECT '男' AS SEX FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL UNION ALL
SELECT '女' FROM DUAL
)
--SQL1:将凡是sex为男的,变成1,否则变成0,看执行结果
SELECT CASE SEX WHEN '男' THEN 1 ELSE 0 END MALE FROM T;
--SQL2:将上面SQL1的结果求一个SUM,就得到了所有SEX为男的数量
SELECT SUM(CASE SEX WHEN '男' THEN 1 ELSE 0 END) MALE FROM T;
--SQL3:同理,将男的数量求一个SUM,将女的数量求一个SUM,CASE WHEN可以构造一个列,这里经常用作行转列
SELECT SUM(CASE SEX WHEN '男' THEN 1 ELSE 0 END) MALE, SUM(CASE SEX WHEN '女' THEN 1 ELSE 0 END) FEMA FROM T;