mysql - 双组依据

Question

我的表格有两列:姓名和年级。它看起来像这样:

NAME  | GRADEAdam  | 1Adam  | 2Adam  | 2Adam  | 3Frank | 2Frank | 1

现在我想创建如下所示的 View :

NAME  | GRADE 1 | GRADE 2 | GRADE 3Adam  |    1    |    2    |     1Frank |    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_3FROM         dbo.RodzajGROUP BY Name

但是没用...我将不胜感激

Answer 1

您正在寻找的东西称为“数据透视表”，它是通过一系列 CASE 语句完成的，这些语句对每个条件应用 1 或 0，然后是 SUM() 增加 1 和 0 以检索计数。

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

请注意，如果您需要它具有动态数量的列，则必须使用脚本语言和循环来构建查询。 (或存储过程中的循环)