>我有这样的表格：
╔════╦════════════╦══════════╗
║ id ║ project_id ║ document ║
╠════╬════════════╬══════════╣
║ 1 ║ 1 ║ 3 ║
║ 2 ║ 1 ║ 5 ║
║ 3 ║ 2 ║ 3 ║
║ 4 ║ 2 ║ 4 ║
║ 5 ║ 3 ║ 5 ║
║ 6 ║ 4 ║ 2 ║
╚════╩════════════╩══════════╝
我想要所有没有 document = 5 的project_id
换句话说，我想要这个结果：
╔════════════╗
║ project_id ║
╠════════════╣
║ 2 ║
║ 4 ║
╚════════════╝
试试这个：
SELECT project_id
FROM mytable
GROUP BY project_id
HAVING COUNT(CASE WHEN document = 5 THEN 1 END) = 0
在这里演示
试试这个
SELECT DISTINCT project_id FROM project
WHERE project_id NOT IN(
SELECT project_id FROM project where document = 5
)
这是一个你可以尝试使用连接的版本：
SELECT t1.project_id
FROM
(
SELECT project_id, COUNT(*) AS projectCount
FROM mytable
GROUP BY project_id
) t1
INNER JOIN
(
SELECT project_id, COUNT(*) AS projectCount
FROM mytable
WHERE document <> 5
GROUP BY project_id
) t2
ON t1.project_id = t2.project_id AND t1.projectCount = t2.projectCount
点击以下链接观看正在运行的演示：
SQLFiddle