不同的JOIN值取决于另一列的值 [英] Different JOIN values depending on the value of another column

问题描述

我有2个表j和c.

两个表都有port和sec列.

Both tables have columns port and sec.

对于j.port = ABC，我想将c.sec的前6个字符与j.sec的前6个字符连接起来.

For j.port = ABC, I want to join the 1st 6 characters of c.sec with the 1st 6 characters of j.sec.

对于其他j.port，我想加入c.sec = j.sec

For other j.ports, I want to join c.sec = j.sec

我该怎么做?

select c.port,j.port,c.sec,j.sec from j, c
where  c.SEC = 
   CASE WHEN j.port = 'ABC' then SUBSTRING(c.sec,1,6) = SUBSTRING(j.sec,1,6)  
   --> something like this
   else j.sec                 

推荐答案

将性能分成两部分可能是有益的.复杂的联接条件将强制嵌套循环.

Performance wise breaking this into two may be beneficial. The complex join condition will force nested loops otherwise.

SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON LEFT(c.sec, 6) = LEFT(j.sec, 6)
WHERE  j.port = 'ABC'
UNION ALL
SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON c.sec = j.sec
WHERE  j.port IS NULL
        OR j.port <> 'ABC' 

或者在这种情况下，您也可以

Or in this specific case you could also do

  SELECT c.port,
       j.port,
       c.sec,
       j.sec
FROM   j
       JOIN c
         ON LEFT(c.sec, 6) = LEFT(j.sec, 6)
         and (j.port = 'ABC' OR c.sec = j.sec)

这使主联接成为简单的等值联接，可以使用任何联接算法，并在结果上带有残留谓词.

This allows the main join to be a simple equi join that can use any of the join algorithms with a residual predicate on the result.

对于下面的示例数据，这两个数据在我的计算机上都花费了大约700ms，而我又在30秒后杀死了三个竞争性答案，因为它们当时都没有完成.

For the following example data both of these took about 700ms on my machine whereas I killed the three competing answers after 30 seconds each as none of them completed in that time.

create table c(port varchar(10), sec varchar(10)  index ix clustered )  
create table j(port varchar(10), sec varchar(10))  

INSERT INTO c 
SELECT TOP 1000000 LEFT(NEWID(),10) , LEFT(NEWID(),10)
FROM sys.all_objects o1, sys.all_objects o2

INSERT INTO j 
SELECT TOP 1000000 LEFT(NEWID(),10) , LEFT(NEWID(),10)
FROM sys.all_objects o1, sys.all_objects o2

这篇关于不同的JOIN值取决于另一列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！