mysql - 为计数和行重用 mysql 查询的主体

Question

因为我使用的是框架 (Magento)，所以我无法直接控制实际执行的 SQL。我可以构建查询的各个部分，但在不同的上下文中，它在进入数据库之前以不同的方式进行修改。

这是我正在使用的一个简化示例。

students        enrolments
--------        ------------------
id| name        student_id|  class
--+-----        ----------+-------
 1| paul                 1|biology
 2|james                 1|english
 3|   jo                 2|  maths
                         2|english
                         2| french
                         3|physics
                         3|  maths

显示所有正在学习英语的学生以及这些学生注册的所有类(class)的查询将是:

SELECT name, GROUP_CONCAT(enrolments.class) AS classes
FROM students LEFT JOIN enrolments ON students.id=enrolments.student_id
WHERE students.id IN ( SELECT e.student_id 
                       FROM enrolments AS e 
                       WHERE e.class LIKE "english" )
GROUP BY students.id

这将给出预期的结果

 name|               classes
 ----+----------------------
 paul|biology, english
james|maths, english, french

如果 Magento 不自动使用我的第一个查询的部分内容，计算学习英语的学生人数将是微不足道的。对于计数，它修改了我的原始查询如下:

1. 删除选中的列。这将是 name 和 classes 列。
2. 向选择添加一个 count(*) 列
3. 删除任何 group by 子句

在这次屠杀之后，我上面的查询变成了

SELECT COUNT(*)
FROM students LEFT JOIN enrolments ON students.id=enrolments.student_id
WHERE students.id IN ( SELECT e.student_id 
                       FROM enrolments AS e 
                       WHERE e.class LIKE "english" )

这不会给我所需的注册英语类(class)的学生人数。相反，它会给我所有注册英语类(class)的学生的总注册人数。

我正在尝试提出一个可以在两种上下文中使用的查询，即计算和获取行。我可以保留任何连接子句和 where 子句，仅此而已。

Answer 1

您的原始查询的问题是 GROUP BY 子句。通过保留 GROUP BY 子句选择 COUNT(*) 将导致两行包含每个用户的多个类:

| COUNT(*) |
|----------|
|        2 |
|        3 |

删除 GROUP BY 子句只会重新调整 LEFT JOIN 中所有行的数量:

| COUNT(*) |
|----------|
|        5 |

我认为 magento 可以解决该问题的唯一方法是将原始查询放入子查询(派生表)并计算结果的行数。但这可能会导致糟糕的表现。我也可以接受一个异常(exception)，提示带有 GROUP BY 子句的查询不能用于分页(或类似的东西)。仅仅返回一个预期的结果可能是图书馆所能做的最糟糕的事情。

Well, it just so happens I have a solution. :-)

在 SELECT 子句中为 GROUP_CONCAT 使用相关子查询。这样您就不需要 GROUP BY 子句。

SELECT name, (SELECT GROUP_CONCAT(enrolments.class)
              FROM enrolments
              WHERE enrolments.student_id = students.id
       ) AS classes
FROM students
WHERE students.id IN ( SELECT e.student_id 
                       FROM enrolments AS e 
                       WHERE e.class LIKE "english" )

但是，我会重写查询以使用 INNER JOIN 而不是 IN 条件:

SELECT s.name, (
    SELECT GROUP_CONCAT(e2.class)
    FROM enrolments e2
    WHERE e2.student_id = s.id
) AS classes
FROM students s
JOIN enrolments e1
  ON e1.student_id = s.id
WHERE e1.class = "english";

这两个查询都将返回与原始查询相同的结果。

| name  | classes              |
|-------|----------------------|
| paul  | biology,english      |
| james | maths,english,french |

但在修改我的 magento 时也会返回正确的计数。

| COUNT(*) |
|----------|
|        2 |

演示:http://rextester.com/OJRU38109

此外 - 由于 MySQL 的优化器，它很有可能会表现得更好，它通常会为使用 JOIN 和 GROUP BY 的查询创建错误的执行计划。