A表:
A1(Primary Key) A2
f001 **
f002 **
f003 **
f004 **
B表:
B1(Primary Key) B2 B3 A1
b001 aaa bbb f001
b002 ccc ddd f002
b003 ccc ddd f003
b004 ddd eee f004现在在查询出A表中关联到B表中(B2,B3)重复项
可以用下面的sql:
SELECT *
FROM A a
WHERE EXISTS (SELECT 1
FROM B b
WHERE (b.B1, b.B2) IN
(SELECT c.B1, c.B2 FROM B c GROUP BY c.B1, c.B2 HAVING COUNT(*) > 1)
AND a.A1= b.A1)请问有简单的sql吗,至少sql 中不出现in,而是exists
A1(Primary Key) A2
f001 **
f002 **
f003 **
f004 **
B表:
B1(Primary Key) B2 B3 A1
b001 aaa bbb f001
b002 ccc ddd f002
b003 ccc ddd f003
b004 ddd eee f004现在在查询出A表中关联到B表中(B2,B3)重复项
可以用下面的sql:
SELECT *
FROM A a
WHERE EXISTS (SELECT 1
FROM B b
WHERE (b.B1, b.B2) IN
(SELECT c.B1, c.B2 FROM B c GROUP BY c.B1, c.B2 HAVING COUNT(*) > 1)
AND a.A1= b.A1)请问有简单的sql吗,至少sql 中不出现in,而是exists
select a.*
from a,b
,(select b1,b2
from b
group by b1,b2
having count(1)>1
)c
where a.a1=b.a1
and b.b1=c.b1
and b.b2=c.b2
;
WHERE EXISTS (SELECT 1
FROM (select * from b b where b.rowid = (select max(rowid) from b c where b.b1=c.b1 and b.b2=c.b2 )) m
where a.a1 =m.b1
SELECT * FROM A a
WHERE EXISTS (SELECT 1
FROM (select * from ( select t.*,row_number() over (partition by b1,b2 order by 1) rn from b ) where rn = 1) m
where a.a1 =m.b1