将自我子查询转换为自我联接 [英] 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屋！