一个sql查询的写法和in查询差不多但是要求同时符合in条件中的每个参数
-1
最佳答案
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
其他回答(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
