关于前端:SQL-CASE-表达式

CASE 表达式分为简略表达式与搜寻表达式，其中搜寻表达式能够笼罩简略表达式的全副能力，我也倡议只写搜寻表达式，而不要写简略表达式。

简略表达式：

SELECT CASE city
WHEN '北京' THEN 1
WHEN '天津' THEN 2
ELSE 0
END AS abc
FROM test

搜寻表达式：

SELECT CASE
WHEN city = '北京' THEN 1
WHEN city = '天津' THEN 2
ELSE 0
END AS abc
FROM test

显著能够看出，简略表达式只是搜寻表达式 a = b 的特例，因为无奈书写任何符号，只有条件换成 a > b 就无奈胜任了，而搜寻表达式岂但能够轻松胜任，甚至能够写聚合函数。

为什么 CASE 表达式里能够写聚合函数？

因为自身表达式就反对聚合函数，比方上面的语法，咱们不会感觉奇怪：

SELECT sum(pv), avg(uv) from test

自身 SQL 就反对多种不同的聚合形式同时计算，所以将其用在 CASE 表达式里，也是顺其自然的：

SELECT CASE
WHEN count(city) = 100 THEN 1
WHEN sum(dau) > 200 THEN 2
ELSE 0
END AS abc
FROM test

只有 SQL 表达式中存在聚合函数，那么整个表达式都聚合了，此时拜访非聚合变量没有任何意义。所以下面的例子，即使在 CASE 表达式中应用了聚合，其实也不过是聚合了一次后，依照条件进行判断罢了。

这个个性能够解决很多理论问题，比方将一些简单聚合判断条件的后果用 SQL 构造输入，那么很可能是上面这种写法：

SELECT CASE
WHEN 聚合函数 (字段) 合乎什么条件 THEN xxx
... 可能有 N 个
ELSE NULL
END AS abc
FROM test

这也能够认为是一种行转列的过程，即 把行聚合后的后果通过一条条 CASE 表达式造成一个个新的列 。

咱们心愿利用 CASE 表达式找出那些 pv 大于平均值的行，以下这种想当然的写法是谬误的：

SELECT CASE
WHEN pv > avg(pv) THEN 'yes'
ELSE 'no'
END AS abc
FROM test

起因是，只有 SQL 中存在聚合表达式，那么整条 SQL 就都是聚合的，所以返回的后果只有一条，而咱们冀望查问后果不聚合，只是判断条件用到了聚合后果，那么就要应用子查问。

为什么子查问能够解决问题？因为子查问的聚合产生在子查问，而不影响以后父查问，了解了这一点，就晓得为什么上面的写法才是正确的了：

SELECT CASE
WHEN pv > (SELECT avg(pv) from test ) THEN 'yes'
ELSE 'no'
END AS abc
FROM test

这个例子也阐明了 CASE 表达式里能够应用子查问，因为子查问是先计算的，所以查问后果在哪儿都能用，CASE 表达式也不例外。

WHERE 前面也能够跟 CASE 表达式的，用来做一些须要非凡枚举解决的筛选。

比方上面的例子：

SELECT * FROM demo WHERE
CASE
WHEN city = '北京' THEN true
ELSE ID > 5
END

原本咱们要查问 ID 大于 5 的数据，但我想对北京这个城市特地看待，那么就能够在判断条件中再进行 CASE 分支判断。

这个场景在 BI 工具里等价于，创立一个 CASE 表达式字段，能够拖入筛选条件失效。

想不到吧，GROUP BY 里都能够写 CASE 表达式：

SELECT isPower, sum(gdp) FROM test GROUP BY CASE
WHEN isPower = 1 THEN city, area
ELSE city
END

下面例子示意，计算 GDP 时，对于十分发达的城市，依照每个区粒度查看聚合后果，也就是看的粒度更细一些，而对于欠发达地区，自身 gdp 也不高，间接依照城市粒度看聚合后果。

这样，就依照不同的条件对数据进行了分组聚合。因为返回行后果是混在一起的，像这个例子，能够依据 isPower 字段是否为 1 判断，是否依照城市、区域进行了聚合，如果没有其余更显著的标识，可能导致无奈辨别不同行的聚合粒度，因而审慎应用。

同样，ORDER BY 应用 CASE 表达式，会将排序后果依照 CASE 分类进行分组，每组依照本人的规定排序，比方：

SELECT * FROM test ORDER BY CASE
WHEN isPower = 1 THEN gdp
ELSE people
END

下面的例子，对发达地区采纳 gdp 排序，否则采纳人口数量排序。

CASE 表达式总结一下有如下特点：

反对简略与搜寻两种写法，举荐搜寻写法。
反对聚合与子查问，须要留神不同状况的特点。
能够写在 SQL 查问的简直任何中央，只有是能够写字段的中央，基本上就能够替换为 CASE 表达式。
除了 SELECT 外，CASE 表达式还广泛应用在 INSERT 与 UPDATE，其中 UPDATE 的妙用是不必将 SQL 拆分为多条，所以不必放心数据变更后对判断条件的二次影响。

探讨地址是：精读《SQL CASE 表达式》· Issue #404 · ascoders/weekly

如果你想参加探讨，请点击这里，每周都有新的主题，周末或周一公布。前端精读 – 帮你筛选靠谱的内容。

关注 前端精读微信公众号

版权申明：自在转载 - 非商用 - 非衍生 - 放弃署名（创意共享 3.0 许可证）

CASE 表达式里的聚合函数