MySQL 用计数连接 3 个表

Question

我有以下表格:

用户

• 编号
• 用户名
• 名字
• 电子邮件

学生

• 编号
• 用户编号

付款

• 编号
• 学号
• 已付费
• 日期

我有以下查询:

SELECT users.username, users.first_name, users.email,
    COUNT(payments.id) as pending_payments
FROM users
LEFT JOIN students ON users.id = students.user_id 
LEFT JOIN payments ON payments.student_id = students.id AND payments.is_paid = 0
WHERE users.username LIKE 'testuser';

当用户名存在并且没有待处理的付款时，它返回:

| username | first_name | email                | pending_payments |

| testuser | test       | testuser@example.com |        0         |

当用户名存在且有 2 笔待付款时，它返回:

| username | first_name | email                 | pending_payments |

| testuser2| test2      | testuser@2example.com |        2         |

但是当用户名不存在时，返回:

| username | first_name | email              | pending_payments   |

| NULL     | NULL       | NULL               |         0          |

相反，当用户名不存在时，我想要的结果是:

空集。

如何修改我的查询以获得预期的结果？(保持用户存在时的行为，但不存在时返回 Empty set，而不是一行将是 NULL 值)。

参见 SQLfiddle:http://sqlfiddle.com/#!9/aab424/11

编辑提供了一个可行的解决方案: http://sqlfiddle.com/#!9/aab424/72

Answer 1

如果没有使用 GROUP BY 子句，像 COUNT(*) 这样的聚合函数总是生成一行。因此，如果您想要一个空结果，请使用 GROUP BY 子句:

SELECT users.username, users.first_name, users.email,
    COUNT(payments.id) as pending_payments
FROM users
LEFT JOIN students ON users.id = students.user_id 
LEFT JOIN payments ON payments.student_id = students.id AND payments.is_paid = 0
WHERE users.username LIKE 'testuser3'
GROUP BY users.username, users.first_name, users.email;

请注意，对于具有该用户名的每个用户，您将获得一行。如果 username 不是 UNIQUE 并且您想为所有具有相同 username 的用户获取一行，您可以使用 HAVING 子句而不是 GROUP BY 来“删除”“空”行。

SELECT users.username, COUNT(payments.id) as pending_payments
FROM users
LEFT JOIN students ON users.id = students.user_id 
LEFT JOIN payments ON payments.student_id = students.id AND payments.is_paid = 0
WHERE users.username LIKE 'testuser3'
HAVING username IS NOT NULL;

SQLFiddle

这与 LEFT 或任何 JOIN 无关。这就是聚合函数的工作方式(在 MySQL 中)，您将在一个没有像这样的连接的简单示例中看到相同的行为

SELECT username, COUNT(*)
FROM users
WHERE username = 'testuser3';

| username | COUNT(*) |
|----------|----------|
|   (null) |        0 |

请注意，此查询不符合 SQL 标准，在严格模式下您将收到错误，因为选择了 username 时没有在 GROUP BY 子句中列出。 (SQLFiddle)。

documentation说:

If you name columns to select in addition to the COUNT() value, a GROUP BY clause should be present that names those same columns. Otherwise, the following occurs:

• If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs: [...]

• If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is indeterminate. The server is free to select the value from any row: [...]

同样:“服务器可以自由地从任何行中选择值”。但是由于没有匹配的行，它返回 NULL。