如果没有更严格的条件，则有条件地回退到不同的加入条件 [英] Conditionally fallback to different join condition if stricter condition not matched

问题描述

我有2个表j和c.

两个表都具有端口和秒列，并且JOIN ON j.ports = c.ports和c.sec = j.sec.

Both tables have columns ports and sec, and JOIN ON j.ports = c.ports and c.sec = j.sec.

对于j.port = 'ABC'，如果相同端口没有c.sec = j.sec，则加入LEFT(c.sec, 6) = LEFT(j.sec, 6)

For j.port = 'ABC', if there is no c.sec = j.sec for the same ports, then JOIN ON LEFT(c.sec, 6) = LEFT(j.sec, 6)

对于其他j.ports，我只想加入j.ports = c.ports and c.sec = j.sec

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

我该怎么做?

示例数据

表c

+------+------------+------------+
| Port |    sec     |   Other    |
+------+------------+------------+
| ABC  | abcdefghij |  ONE       |
| ABC  | klmnop     |  TWO       |
| LMN  | qwertyuiop |  THREE     |
| XYZ  | asdfghjkl  |  FOUR      |
+------+------------+------------+

表j

+------+------------+
| Port |    sec     |
+------+------------+
| ABC  | abcdefxxxx |
| ABC  | klmnop     |
| LMN  | qwertyuiop |
| XYZ  | zxcvbnm    |
+------+------------+

已所需结果

+------+------------+------------+
| Port |    sec     |  other     |
+------+------------+------------+
| ABC  | abcdefghij |  ONE       |  --> mactching on sec's 1st 6 characters 
| ABC  | klmnop     |  TWO       |  --> mactching on sec
| LMN  | qwertyuiop |  THREE     |  --> mactching on sec
+------+------------+------------+

推荐答案

一种方法是仅在不太严格的谓词上进行内部联接，然后在c.port = 'ABC'和更严格的条件下使用排名函数丢弃不需要的行匹配了特定的c.port, c.sec组合.

One way would be to just inner join on the less strict predicate then use a ranking function to discard unwanted rows in the event that c.port = 'ABC' and the stricter condition got a match for a particular c.port, c.sec combination.

with cte as
(
select c.port as cPort, 
       c.sec as cSec, 
       c.other as other,
       j.sec as jSec, 
       RANK() OVER (PARTITION BY c.port, c.sec ORDER BY CASE WHEN c.port = 'ABC' AND j.sec = c.sec THEN 0 ELSE 1 END) AS rnk
from c inner join  j on left(j.sec,6) = left(c.sec,6)
)
SELECT cPort, cSec, other, jSec
FROM cte 
WHERE rnk = 1

这篇关于如果没有更严格的条件，则有条件地回退到不同的加入条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！