现有1张表:
id userId ringId
1 1000 111
2 1000 222
3 1000 3334 1001 111
5 1001 444
6 1001 555我要查询出userId为1001的结果集,同时跟userId为1000的结果集用ringId进行匹配,如果ringId相同,则标记为1,不相同标记为0,查询到的结果如下:id userId ringId state
4 1001 111 1
5 1001 444 0
6 1001 555 0求sql。sqloracle
id userId ringId
1 1000 111
2 1000 222
3 1000 3334 1001 111
5 1001 444
6 1001 555我要查询出userId为1001的结果集,同时跟userId为1000的结果集用ringId进行匹配,如果ringId相同,则标记为1,不相同标记为0,查询到的结果如下:id userId ringId state
4 1001 111 1
5 1001 444 0
6 1001 555 0求sql。sqloracle
with t as(
select 1 id,'1000' userId,'111' ringId from dual
union all
select 2,'1000','222' from dual
union all
select 3,'1000','333' from dual
union all
select 4,'1001','111' from dual
union all
select 5,'1001','444' from dual
union all
select 6,'1001','555' from dual
)
select t1.*,
(select count(1)
from t t2
where t2.userid = '1001'
and t2.ringid = t1.ringid
and rownum = 1) state
from t t1
where userid = '1000'; ID USERID RINGID STATE
---------- ------ ------ ----------
1 1000 111 1
2 1000 222 0
3 1000 333 0
select 1 id,'1000' userId,'111' ringId from dual
union all
select 2,'1000','222' from dual
union all
select 3,'1000','333' from dual
union all
select 4,'1001','111' from dual
union all
select 5,'1001','444' from dual
union all
select 6,'1001','555' from dual
)
select t1.*,
(select count(1)
from t t2
where t2.userid = '1000'
and t2.ringid = t1.ringid
and rownum = 1) state
from t t1
where userid = '1001';
ID USERID RINGID STATE
---------- ------ ------ ----------
4 1001 111 1
5 1001 444 0
6 1001 555 0
userid,
ringid,
sign((select count(*)
from table b
where userid = 1000
and a.ringid = blringid)) state
from table a
where a.userid = 1001
userid,
ringid,
sign((select count(*)
from table b
where userid = 1000
and a.ringid = b.ringid)) state
from table a
where a.userid = 1001
如果不指定的话可以查询表然后自身全表扫描一面userid不是当前的,上sql
with t as(
select 1 as id,'1000' as userId,'111' as ringId from dual
union all
select 2,'1000','222' from dual
union all
select 3,'1000','333' from dual
union all
select 4,'1001','111' from dual
union all
select 5,'1001','444' from dual
union all
select 6,'1001','555' from dual
)
select t1.*,
(select count(1)
from t t2
where t1.userid <> t2.userid
and t1.ringId = t2.ringId) as stat
from t t1;