mysql - 按不同年级分组并知道他们是多少年级

Question

我有这样的 table :

Problem    UserID  Grade
Probleme1  ID1     33
Probleme1  ID2     100
Probleme1  ID4     57
Probleme1  ID6     57
Probleme1  ID78    24
Probleme1  ID5     24
Probleme2  ID1     37
Probleme2  ID12    88
Probleme2  ID6846  100
Probleme2  ID2     2

我想要实现的目标是创建一个包含以下内容的表:

1. 对于每个问题，整个成绩范围(从 0 到 100，即使没有人在该问题上得到 87，我希望它显示为 0 值)
2. 对于每个等级(对于每个问题)，获得该等级的人数。

所以我尝试这样做:

DROP TABLE IF EXISTS Problem_Grades_Point;
CREATE TABLE Problem_Grades_Point (
  Platform VARCHAR(20),
  AnalyticsDomain VARCHAR(255),
  ShortTitle VARCHAR(255),
  Problem VARCHAR(255),
  Grade0 INT,
  Grade10 INT,
  Grade20 INT,
  INDEX (Problem)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO Problem_Grades_Point
SELECT
  Platform,
  AnalyticsDomain,
  ShortTitle,
  Problem,
  COUNT(Problem) AS Grade0,
  0 as Grade10,
  0 as Grade20
FROM First_Attempt_Problem_Grades
WHERE First_Grade = 0
GROUP BY Problem

UNION ALL 

SELECT
  Platform,
  AnalyticsDomain,
  ShortTitle,
  Problem,
  0 as Grade0,
  COUNT(t.Problem) AS Grade10,
  0 as Grade20
  FROM First_Attempt_Problem_Grades t
WHERE First_Grade = 10
GROUP BY Problem

UNION ALL 

SELECT
  Platform,
  AnalyticsDomain,
  ShortTitle,
  Problem,
  0 as Grade0,
  0 as Grade10,
  COUNT(Problem) AS Grade20
  FROM First_Attempt_Problem_Grades
WHERE First_Grade = 20
GROUP BY Problem

当然，它并不包含我想要的所有内容，当然，它也不起作用。它仅显示成绩 0 的结果。所有其他成绩均为空，如下所示:

The Mysql's query result

我该如何建立这个 SOV 社区？ :)

Answer 1

首先您需要一份成绩列表。因此，您创建一个成绩从 1 到 100 的数字表，并将其命名为 Grades

How do I get a list of numbers in MySQL?

现在你可以:

SELECT P.Problem,
       G.Grade,
       COUNT(Y.UserID)
FROM ( SELECT DISTINCT Problem
       FROM YourTable) as P  -- if you have a table `Problems` you can use it instead
CROSS JOIN Grades as G
LEFT JOIN YourTable Y
  ON P.Problem = Y.Problem
 AND G.Grade = Y.Grade
GROUP BY P.Problem, G.Grade