将自我子查询转换为自我联接 [英] Converting a self subquery to a self join
本文介绍了将自我子查询转换为自我联接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想知道是否有一种方法可以将self子查询转换为self join 这是自我子查询
I was wondering if there was a way to convert a self subquery to a self join Here is the self subquery
SELECT a,
b
FROM c AS t1
WHERE ( b IN (SELECT b
FROM c AS t2
WHERE ( t1.b = b )
AND ( t1.e <> e )) )
推荐答案
如果只想查找重复项,则EXIST
可能会更快:
If you only want to find the duplicates an EXIST
would probably be faster:
SELECT a,b FROM c WHERE EXISTS(SELECT NULL FROM c c2 WHERE c2.b=c.b AND c2.e<>c.e)
如果您想将每条记录与其重复项连接在一起,但每条记录仅获得一条记录:
If you want to join every record with its duplicate but get only one record for each:
select t1.a
, t1.b
, t1.e as t1e
, t2.e as t2e
from c as t1
inner join c as t2
on t1.b = t2.b
and t1.e > t2.e
(请注意,我使用的是>
而不是<>
)
(note that i've used >
instead of <>
)
这篇关于将自我子查询转换为自我联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文