首页 新闻 搜索 专区 学院

一个sql查询的写法和in查询差不多但是要求同时符合in条件中的每个参数

-1
悬赏园豆:20 [已解决问题] 解决于 2010-08-01 22:50

用户ID    用户拥有服务ID       
1           1
1           2
1           3
2           1
2           3
怎么查出同时拥有3和2的用户啊

leo刘的主页 leo刘 | 初学一级 | 园豆:156
提问于:2010-07-31 23:23
< >
分享
最佳答案
1
-- SQL Server 2005/2008
with user_service as(
select 1 as userid,1 as serviceid union all
select 1,2 union all
select 1,3 union all
select 2,1 union all
select 2,3
),
srvc
as (
select 1 as serviceid union all
select 2
)
select userid
from user_service u inner join srvc s
on u.serviceid=s.serviceid
group by userid
having count(userid)=(select COUNT(*) from srvc)

-- oracle 10g
with user_service as(
select 1 as userid,1 as serviceid from dual union all
select 1,2 from dual union all
select 1,3 from dual union all
select 2,1 from dual union all
select 2,3 from dual
),
srvc
as (
select 1 as serviceid from dual union all
select 2 from dual
)
select userid
from user_service u inner join srvc s
on u.serviceid=s.serviceid
group by userid
having count(userid)=(select COUNT(*) from srvc)

USERID
----------
         1

 

收获园豆:20
killkill | 小虾三级 |园豆:992 | 2010-08-01 00:23
其他回答(1)
0

 

 

1 select
2 t1.userid
3  from mytab t1
4  inner join mytab t2
5  on t1.userid=t2.userid and t1.serviceid=2 and t2.serviceid=3

 

 

zhangxd | 园豆:175 (初学一级) | 2010-08-01 19:00
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册