我的表有两列:名称和等级.看起来像这样:
NAME | GRADE
Adam | 1
Adam | 2
Adam | 2
Adam | 3
Frank | 2
Frank | 1
现在,我想创建如下所示的视图:
NAME | GRADE 1 | GRADE 2 | GRADE 3
Adam | 1 | 2 | 1
Frank | 1 | 1 | 0
我写了这个:
SELECT Name,
(SELECT COUNT(Grade)
FROM dbo.Rodzaj
WHERE Grade = '1') as Grade_1,
(SELECT COUNT(Grade)
FROM dbo.Rodzaj
WHERE Grade = '2) as Grade_2,
(SELECT COUNT(Grade)
FROM dbo.Rodzaj
WHERE Grade = '3') as Grade_3
FROM dbo.Rodzaj
GROUP BY Name
但这行不通…
我将不胜感激任何帮助
解决方法:
您要查找的内容称为“数据透视表”,它由一系列CASE语句完成,这些语句对每个条件应用1或0,然后对1和0进行SUM()检索计数.
SELECT
NAME,
SUM(CASE WHEN GRADE = 1 THEN 1 ELSE 0 END) AS GRADE1,
SUM(CASE WHEN GRADE = 2 THEN 1 ELSE 0 END) AS GRADE2,
SUM(CASE WHEN GRADE = 3 THEN 1 ELSE 0 END) AS GRADE3
FROM Rodzaj
GROUP BY NAME
请注意,如果您需要具有动态的列数,则必须使用脚本语言和循环来构造查询. (或存储过程中的循环)
标签:sql,mysql
来源: https://codeday.me/bug/20191201/2078623.html