SQL 计算行上给定复合条件的值的数量

发布于 2024-11-09 21:44:53 字数 268 浏览 1 评论 0原文

我有一个如下表：


User ID  Service
1        2
1        3
2        1
2        3
3        5
3        3
4        3
5        2

如何构造一个查询，在其中计算拥有 3 项服务和至少一项其他服务的所有用户 ID？

在上表中，我感兴趣的查询将返回 3，因为用户 ID 1、2 和 3 拥有服务 3 和至少一项其他服务。

谢谢！

原文

I have a table as follows:


User ID  Service
1        2
1        3
2        1
2        3
3        5
3        3
4        3
5        2

How could I construct a query where I would count all the user ids that have a service of 3 and at least one other service?

In the above table, the query I'm interested in would return 3 because user ids 1, 2 and 3 have service of 3 and at least one other service.

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

絕版丫頭 2024-11-16 21:44:53

在 MS SQL 中：

select count(*)
from UserService
where 
    ServiceId = 3 and
    UserId in (select UserId from UserService where ServiceId != 3)

In MS SQL:

select count(*)
from UserService
where 
    ServiceId = 3 and
    UserId in (select UserId from UserService where ServiceId != 3)

回复收藏 0 原文

奶气 2024-11-16 21:44:53

编辑回应评论：

select count(*) from (

select userId
  from theTable
 group by userId
having sum(case when service = 3 then 1 else 0 end) > 0
   and sum(case when service <>3 then 1 else 0 end) > 0

) x

EDIT In response to comments:

select count(*) from (

select userId
  from theTable
 group by userId
having sum(case when service = 3 then 1 else 0 end) > 0
   and sum(case when service <>3 then 1 else 0 end) > 0

) x

回复收藏 0 原文

~没有更多了~