SQL - 使用MAX()实现行转列的一个坑 - 当GROUP BY分组后，相同粒度下的记录多于一条时，需要多加注意

    昨天遇到了一个问题，使用 MAX() 进行行转列时出现错误。然后第一次在 Stack Overflow 问问题，有幸得到大佬的帮助，贴上地址：MySQL-What’s the difference between these two sql?CASE WHEN in MAX(),one uses null,one uses a character

问题描述

    首先看一下源数据Courses：

name	course
Marry	UNIX
Eric	SQL
Bob	Java
Cherry	SQL
John	SQL
John	UNIX
Bob	SQL

    现在希望得到下面的结果：

name	SQL	UNIX	JAVA
Bob	○	x	○
Cherry	○	x	x
Eric	○	x	x
John	○	○	x
Marry	x	○	x

    这还不简单？直接套模板：

SELECT name,
   max(CASE WHEN course = 'SQL' THEN '○' ELSE 'x' END) AS "SQL",
   max(CASE WHEN course = 'UNIX' THEN '○' ELSE 'x' END) AS "UNIX",
   max(CASE WHEN course = 'Java' THEN '○' ELSE 'x' END) AS "Java"
FROM Courses
GROUP BY name;

    哎呦尼玛，结果怎么就错了

name	SQL	UNIX	JAVA
Bob	x	x	x
Cherry	○	x	x
Eric	○	x	x
John	x	x	x
Marry	x	○	x

    经大佬指点，原来 ‘○’ 不是 ‘o’，而且这个与 ‘○’ 和 ‘x’ 的大小有关。那我们看一下这两个的大小：

SELECT CASE WHEN '○' > 'x' THEN 'true' ELSE 'false' END;

运行结果：false

    所以 ‘○’ 的大小小于 ‘x’，‘○’ < ‘x’。

查找原因

    为了简化问题，我将SQL改成这样：

SELECT name,
  max(CASE WHEN course = 'SQL' THEN 1 ELSE 2 END) AS "SQL",
  max(CASE WHEN course = 'UNIX' THEN 1 ELSE 2 END) AS "UNIX",
  max(CASE WHEN course = 'Java' THEN 1 ELSE 2 END) AS "Java"
FROM Courses
GROUP BY name;

    运行结果如下（错误）：

name	SQL	UNIX	JAVA
Bob	2	2	2
Cherry	1	2	2
Eric	1	2	2
John	2	2	2
Marry	2	1	2

    我们希望的正确结果如下：

name	SQL	UNIX	JAVA
Bob	1	2	1
Cherry	1	2	2
Eric	1	2	2
John	1	1	2
Marry	2	1	2

    那问题来了，为什么 1 会被 2 覆盖了？MAX() 里面不就两个分支吗？如果有对应的course就为1，否则为2？
    原因很简单，以Bob为例，GROUP BY name，Bob有一条记录是SQL的，那么执行一次SELECT后的语句就有 1 2 2，然后有一条记录是Java的，执行分支语句后为 2 2 1，由于这里使用了 MAX()，如果一列中有更大的，则选择更大的，所以最终结果一个为 2 2 2

				SQL UNIX JAVA
Bob SQL  ---->   1    2    2    <----之前没数据，执行了MAX()的结果
Bob JAVA ---->   2    2    1    <----只执行了MAX()里面的分支语句，还没执行MAX()
		 ---->   2    2    2    <----执行了MAX()的结果

    简单来说就是，当GROUP BY分组后，相同粒度下的记录多于一条时（比如name粒度（相当于主键，用于区分记录）下，Bob有两条记录），如果ELSE分支后面的数比前面的数大，由于MAX()会取最大值，那么之前记录中有true的记录会被覆盖。

解决方案

1. 利用聚合函数忽略null的特点

SELECT name,
       max(CASE WHEN course = 'SQL' THEN '○' ELSE null END) AS "SQL",
       max(CASE WHEN course = 'UNIX' THEN '○' ELSE null END) AS "UNIX",
       max(CASE WHEN course = 'Java' THEN '○' ELSE null END) AS "Java"
 FROM Courses
GROUP BY name;

name	SQL	UNIX	JAVA
Bob	○	(null)	○
Cherry	○	(null)	(null)
Eric	○	(null)	(null)
John	○	○	(null)
Marry	(null)	○	(null)

2. 如果要用字符或数的话，要确保true分支的字符（数）要大于else分支的字符（数），否则会覆盖。

SELECT name,
   max(CASE WHEN course = 'SQL' THEN 'b' ELSE 'a' END) AS "SQL",
   max(CASE WHEN course = 'UNIX' THEN 'b' ELSE 'a' END) AS "UNIX",
   max(CASE WHEN course = 'Java' THEN 'b' ELSE 'a' END) AS "Java"
FROM Courses
GROUP BY name;

3. CASE WHEN嵌套 + SUM()

SELECT  name,
	CASE WHEN SUM(CASE WHEN course = 'SQL' THEN 1 ELSE 0 END) >= 1
			 THEN '○' ELSE 'x' END AS "SQL",
	CASE WHEN SUM(CASE WHEN course = 'UNIX' THEN 1 ELSE 0 END) >= 1
			 THEN '○' ELSE 'x' END AS "UNIX",
	CASE WHEN SUM(CASE WHEN course = 'Java' THEN 1 ELSE 0 END) >= 1
			 THEN '○' ELSE 'x' END AS "Java"
FROM Courses
GROUP BY name;