SQL进阶教程第一章--------CASE表达式练习题

　　CASE表达式是支撑SQL声明式编程的根基之一，也是灵活运用SQL时不可或缺的基础技能。

　　CASE表达式中有END在执行时会被判定为一个固定值，因此它可以写在聚合函数内部，也正因为它是表达式，所以还可以写在SELECT子句、GROUP BY子句、WHERE子句、ORDER BY子句里。简单点说，在能写列名和常量的地方，通常都能够写CASE表达式。

　　练习题

　　1.多列数据的最大值

　　SQL从多行数据里选出最大值或者最小值很容易——通过GROUP BY子句对合适的列进行聚合操作，并使用MAX或者MIN聚合函数就可以求出。那么从多列数据里面选出最大值应该怎么做呢？

　　

 

答案1：

SELECT key1, CASE WHEN x>=y THEN x
                  ELSE y END AS greatest
FROM greatests

答案2：ORACLE和MySQL数据库直接提供了可以实现这个需求的greatest函数。

SELECT key1, greatest(x,y)
FROM greatests

同理，找出x y z中的最大值：

　　

SELECT key1, CASE WHEN x>=y AND x>=z THEN x
                  WHEN y>=x    AND y>=z THEN y
                  ELSE z END AS greatest                        
FROM greatests

 

SELECT key1,greatest(x,y,z)
FROM greatests

 

练习题2：转换行列——在表头里面加入汇总和再揭

　　再揭：这里指的是在表格中以合计值的形式再次体现德岛、香川、爱媛和高知这四个县的数据。

　　使用PopTb2作为样本数据

　　

　　练习一下将行结构转换为列结构的数据，这次生成下面这样的表头里面带有汇总和再揭的二维表。

 　　

　　

　　

SELECT CASE WHEN sex = '1'  THEN '男'
            WHEN sex = '2'    THEN '女'
            ELSE NULL END AS '性别',
            SUM(population) AS '全国',
            SUM(CASE WHEN pref_name ='德岛' THEN population 
                     ELSE 0 END) AS '德岛',
            SUM(CASE WHEN pref_name ='香川' THEN population 
                     ELSE 0 END) AS '香川',
            SUM(CASE WHEN pref_name ='爱媛' THEN population 
                     ELSE 0 END) AS '爱媛',
            SUM(CASE WHEN pref_name ='高知' THEN population 
                     ELSE 0 END) AS '高知',
            SUM(CASE WHEN pref_name IN ('德岛','香川','爱媛','高知') THEN population
                     ELSE 0 END) AS '四国'
FROM PopTb2
GROUP BY CASE WHEN sex = '1'  THEN '男'
              WHEN sex = '2'    THEN '女'
              ELSE NULL END 

 　　3.练习题3：用ORDER BY生成按照某种指定顺序进行排序。

　　　对练习题1用过的表Greatests正常执行SELECT key FROM Greatests ORDER BY key; 这个查询之后，结果会按照key这一列值的字母表顺序显示出来。

　　那么请思考一个查询语句，使得结果按照B-A-D-C这样的执行顺序进行排列。这个顺序并没有什么具体的意义，只是单纯地希望能够按照此顺序进行排序。

 

 答案：

SELECT *
FROM greatests
ORDER BY CASE WHEN key1 = 'B' THEN 1
              WHEN key1 = 'A' THEN 2
              WHEN key1 = 'D' THEN 3
              WHEN key1 = 'C' THEN 4
              ELSE NULL END