对没有相同列的行组执行联合。正在尝试生成空直方图库

Question

我正在使用MySQL。

我有一个表grades，看起来如下所示：

course_id grade
101        90
101        100
101        100
102        92
102        85
102        90

我有一个视图，使用下面的查询计算每个类在每个bin中有多少个等级。

SELECT
    *,
    COUNT(grade_bin) as count
FROM
    (SELECT
        course_id, 
        FLOOR(grade/5.00)*5 AS grade_bin
    FROM ranked_grades) as t
        GROUP BY course_id, grade_bin
        ORDER BY course_id, grade_bin ASC;

这将返回：

course_id grade_bin count
101         90        1
101         100       2
102         85        1
102         90        2

我想在此视图中添加空箱，如下所示：

course_id grade_bin count
101          40       0
101          45       0
...         ...      ...
101          100      2
102          40       0
...         ...      ...

因此，箱增加5，从40开始，到100停止。我的想法是为空bin创建一个表，插入每个等级bin和一个0计数，然后使用union，但我无法使union工作，因为我在空bin表中没有course_id列（我也不想--有很多，而且它们可能会更改）。如何对每组course_ids进行联合？

Answer 1

从一个箱列表开始。然后使用交叉联接生成所有行。最后，加入现有数据。

所以：

SELECT c.course_id, gb.grade_bin, COUNT(rg.course_id) as cnt
FROM (SELECT 40 as grade_bin UNION ALL
      SELECT 45 as grade_bin UNION ALL
      SELECT 50 as grade_bin UNION ALL
      . . . 
      SELECT 100 as grade_bin UNION ALL
     ) gb CROSS JOIN
     (SELECT DISTINCT course_id
      FROM ranked_grades 
     ) c LEFT JOIN
     ranked_grades rg
     ON rg.course_id = c.course_id AND
        rg.grade >= gb.grade_bin AND
        rg.grade < gb.grade_bin + 5
GROUP BY c.course_id, gb.grade_bin;

注意：这将从ranked_grades表中检索课程。您还可以使用courses表，该表可能也是可用的。