MySQL 按问题分组

Question

我有一个票证表，需要选择与某个用户不关联的唯一票证列表。我不能只说“WHERE user_id != 10”，因为 GROUP BY 语句会影响返回哪个 user_id。

最后，我正在使用 MySQL 数据库。

SELECT * FROM tickets;
+----+---------+--------+
| id | user_id | ticket |
+----+---------+--------+
|  1 |      10 | T12345 |
|  2 |      11 | T23456 |
|  3 |      12 | T34567 |
|  4 |      10 | T34567 |
|  5 |      13 | T45678 |
+----+---------+--------+

SELECT * FROM tickets WHERE user_id != 10 GROUP BY ticket;
+----+---------+--------+
| id | user_id | ticket |
+----+---------+--------+
|  2 |      11 | T23456 |
|  3 |      12 | T34567 |
|  5 |      13 | T45678 |
+----+---------+--------+

-- Needed result
-- User #10 also worked with ticket T34567,
-- so I do not want it listed in the results
+----+---------+--------+
| id | user_id | ticket |
+----+---------+--------+
|  2 |      11 | T23456 |
|  5 |      13 | T45678 |
+----+---------+--------+

有人可以帮助我构建一个查询来返回我需要的结果吗？

谢谢你！

Answer 1

这应该可以解决问题：

SELECT *
FROM tickets
WHERE ticket NOT IN (SELECT ticket FROM tickets WHERE user_id = 10)

这会从票证表中选择与用户 (id = 10) 拥有的票证不匹配的所有票证。

GROUP BY 子句旨在（如 OrangeDog 所说）用于聚合查询。例如，如果您要查找票证的用户数量，您可以使用：

SELECT COUNT(*) AS tcnt, ticket
FROM tickets
GROUP BY ticket

这将为您提供：

+------+--------+
| tcnt | ticket |
+------+--------+
|    1 | T12345 |
|    1 | T23456 |
|    2 | T34567 |
|    3 | T45678 |
+------+--------+

因为您在这里没有做类似的事情，所以没有必要。

Answer 2

SELECT *
FROM tickets 
WHERE ticket NOT IN (SELECT ticket FROM tickets WHERE user_id = 10);

GROUP BY 适用于使用聚合函数（COUNT、SUM 等）。如果你不这样做，它通常不会做任何有用的事情。