昨天遇到了一个问题,使用 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