mysql - 需要简单的 SQL 查询帮助

Question

我有这张 table

uid  |  rid
-----------
1    |    4
1    |   13
2    |    4
3    |   13

我希望我的查询返回具有 rid = 13 且没有 rid = 4 的 uid，因此结果为 3

目前为止

SELECT distinct uid
FROM test
WHERE 
ur.rid <> 4 
AND ur.rid = 13

当然这会返回 1 和 3

为了只得到 3 个，我在查询中遗漏了什么？

非常感谢。

Answer 1

您可以添加NOT EXISTS:

SELECT DISTINCT uid
FROM test t1
WHERE t1.rid = 13
  AND NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.uid = t1.uid
                    AND t2.rid = 4);

LiveDemo

或使用聚合:

SELECT uid
FROM test
GROUP BY uid
HAVING COUNT(CASE WHEN rid = 13 THEN 1 END) > 0
   AND COUNT(CASE WHEN rid = 4 THEN 1 END) = 0;

LiveDemo2

编辑(戈登):

上面的答案很好。我只是将其添加为对第二个查询的简化(放入单独的答案似乎不合适，而且评论太长):

SELECT uid
FROM test
GROUP BY uid
HAVING SUM(rid = 13) > 0 AND
       SUM(rid = 4) = 0;

它利用了 MySQL 的一个很好的特性。

SqlFiddleDemo