exists 查询多字段重复

[lanita]

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

[虫洞]

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
;

[秋雨飘落]

SELECT * FROM A a
 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
        

[物润声无]

又要复习一下sql了~~

[huanjihu33]

[Quote=引用 2 楼 zftang 的回复:]
SQL code

SELECT * FROM A a
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
……
[/Quote]
这三个的答案的结果有点不一样