从指定范围内的表中获取条目数

Question

我正在尝试使用 MySQL 获取大于其他表中某些值的条目数。

我有与这些类似的表格（但更大）：

CREATE TABLE test_ranges AS SELECT 'class 1' AS category, 'A' AS grade, 90 AS points;
INSERT INTO test_ranges VALUES ('class 1', 'B', 75),('class 2', 'A', 80);
CREATE TABLE test_results AS SELECT 'class 1' AS category, 100 AS points;
INSERT INTO test_results VALUES ('class 1', 95),('class 1', 94),('class 1', 85),('class 1', 55),('class 1', 44),('class 1', 33);

还有表格的内容：

SELECT * FROM test_ranges;
+----------+-------+--------+
| category | grade | points |
+----------+-------+--------+
| class 1  | A     |     90 |
| class 1  | B     |     75 |
| class 2  | A     |     80 |
+----------+-------+--------+

对于结果：

SELECT * FROM test_results;
+----------+--------+
| category | points |
+----------+--------+
| class 1  |    100 |
| class 1  |     95 |
| class 1  |     94 |
| class 1  |     85 |
| class 1  |     55 |
| class 1  |     44 |
| class 1  |     33 |
+----------+--------+

我希望每个年级类型的每个类别都有条目数。

当我尝试使用 LEFT JOIN 进行选择时，它几乎可以像我想要的那样工作，但计数不正确。

SELECT test_ranges.category,grade,count(*) FROM test_ranges
  LEFT JOIN test_results USING(category)
  GROUP BY category,grade;
+----------+-------+----------+
| category | grade | count(*) |
+----------+-------+----------+
| class 1  | A     |        7 |
| class 1  | B     |        7 |
| class 2  | A     |        1 |
+----------+-------+----------+

当我添加WHERE子句时，我得到了正确的计数值，但不是每个category和grade：

SELECT test_ranges.category,grade,COUNT(*) FROM test_ranges
  LEFT JOIN test_results USING(category)
  WHERE test_results.points>=test_ranges.points
  GROUP BY category,grade;
+----------+-------+----------+
| category | grade | count(*) |
+----------+-------+----------+
| class 1  | A     |        3 |
| class 1  | B     |        4 |
+----------+-------+----------+

我希望得到类似于以下内容的结果：

+----------+-------+----------+
| category | grade | count(*) |
+----------+-------+----------+
| class 1  | A     |        3 |
| class 1  | B     |        4 |
| class 2  | A     |        0 |
+----------+-------+----------+

编辑

我发现：

SELECT test_ranges.category,grade,COUNT(test_results.points) FROM test_ranges
  LEFT JOIN test_results USING(category)
  WHERE test_results.points>=test_ranges.points OR test_results.points IS NULL
  GROUP BY category,grade;

返回正确的东西。这里的问题只是性能。在我的原始问题中，test_ranges是（查询连接查询）语句，结果给出大约 32 行，test_results有 300k 行。

Answer 1

使用 on 子句：

select rg.category, rg.grade, ifnull(count(rs.points),0) as num
  from test_ranges rg
  left join test_results rs
       on rg.category = rs.category
      and rs.points >= rg.points
 group by rg.category, rg.grade;

小提琴：http://sqlfiddle.com/#!9/d503a/4/0

正如戈登所指出的，你的预期结果有点奇怪。这表明每个获得 A 的人也获得了 B，因为他们达到了该阈值。我不确定这是否是你真正想要的。我想你可以把它想象成"至少获得X级的人数......"，在这方面可能有用。无论如何，请注意。

Answer 2

从test_ranges开始，并使用相关的子查询：

select tr.category, tr.grade,
       (select count(*)
        from test_results tr2
        where tr2.category = tr.category and
              tr2.points >= tr.points
       ) as cnt
from test_ranges tr;

这实际上应该返回查询返回的内容。