本文介绍了如何检查每个组中是否存在值(分组后)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设我有一个订阅表:

uid  | subscription_type 
------------------------  
Alex | type1
Alex | type2
Alex | type3
Alex | type4
Ben  | type2
Ben  | type3
Ben  | type4

并且只想选择拥有2个以上订阅但从未订阅过1类的用户

And want to select only the users that have more than 2 subscriptions but never subscribed with type 1

预期结果仅选择 Ben。

The expected result is selecting "Ben" only.

我很容易找到拥有2个以上用户的用户订阅使用:

I easy can found the users that have more than 2 subscribes using:

SELECT uid
FROM subscribes
GROUP BY uid
HAVING COUNT(*) > 2

但是如何检查组中是否不存在某些值?

But how to check if in a group some value never exists?

感谢您的帮助!

推荐答案

尝试以下查询:

SELECT uid 
FROM subscribes 
GROUP BY uid 
HAVING COUNT(*) > 2
   AND max( CASE "subscription_type"  WHEN 'type1' THEN 1 ELSE 0 END ) = 0

这篇关于如何检查每个组中是否存在值(分组后)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-24 15:15