SQL 如何检查每个组中是否存在值（分组后）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33784786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if value exists in each group (after group by)
提问by Spivakos
Assume I have a subscriptions table :
假设我有一个订阅表：
uid | subscription_type
------------------------
Alex | type1
Alex | type2
Alex | type3
Alex | type4
Ben | type2
Ben | type3
Ben | type4
And want to select only the users that have more than 2 subscriptions but never subscribed with type 1
并且只想选择订阅超过 2 个但从未订阅过类型 1 的用户
The expected result is selecting "Ben" only.
预期的结果是仅选择“Ben”。
I easy can found the users that have more than 2 subscribes using:
我很容易找到使用以下方法订阅超过 2 个的用户：
SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2
But how to check if in a group some value never exists?
但是如何检查组中是否有某个值从不存在？
Thanks for the help!
谢谢您的帮助！
回答by krokodilko
Try this query:
试试这个查询：
SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2
AND max( CASE "subscription_type" WHEN 'type1' THEN 1 ELSE 0 END ) = 0
回答by wildplasser
To check if something doesn't exist, use NOT EXISTS(...):
要检查某些东西是否不存在，请使用NOT EXISTS(...)：
SELECT uid
FROM subscribes su
WHERE NOT EXISTS (SELECT *
FROM subscribes nx
WHERE nx.uid = su.uid AND nx.subscription_type = 'type1'
)
GROUP BY uid HAVING COUNT(*) > 2
;
回答by A. Greensmith
Create Sample Table:
创建示例表：
CREATE TABLE subscribes
(
uid NVARCHAR(MAX),
subscription_type NVARCHAR(MAX)
)
Insert Values:
插入值：
INSERT INTO subscribes
VALUES ('Alex', 'type1'), ('Alex', 'type2'), ('Alex', 'type3'), ('Alex', 'type4'), ('Ben', 'type2'), ('Ben', 'type3'), ('Ben', 'type4')
SQL Query:
SQL查询：
SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2
AND MAX(CASE subscription_type WHEN 'type1' THEN 1 ELSE 0 END) = 0
Output:
输出：
======
|uid |
------
|Ben |
======
