根據多個條件連接兩個表

我有兩張桌子產品和職位。

產品：

id BIGINT PRIMARY KEY,
product VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)

職位：

id BIGINT PRIMARY KEY,
position VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)

每個表都有 criterium1 和 criterium2。我想要一個包含職位和產品之間連接的結果集。對於每個職位，我都想要在 criterium1 或 criterium2 上匹配的相應產品；當 criterium1 找不到或為 NULL 時，它需要在 criterium2 上匹配。

SQL 語句會是什麼樣子？

到目前為止，我已經嘗試過：

SELECT * FROM positions pos 
INNER JOIN products pro 
ON COALESCE(pos.criterium1, pos.criterium2,'') 
   = COALESCE( pro.criterium1, pro.criterium2,'') 

pro.criterium1但是，這與topos.criterium1或pro.criterium2to不匹配pos.criterium2。

補充：對不起，我的要求有點模糊。讓我試著完成畫圖。

我們使用的是 SQL Server 2005。

products:

id   product   criterium1   criterium2
1    pro1       AAA1         910
2    pro2       106          BB2
3    pro3       AB1          XXY

positions:

id  position  criterium1  criterium2
1    pos1       NULL         910
2    pos2       106          CCC
3    pos3       XXX          BB2
4    pos4       AA1          XXY
5    pos5       NULL         123

我正在尋找的是使用條件匹配加入表格，我想將位置與產品匹配。首先，我想在 criterium1 上進行匹配。如果在 criterium1 上找不到匹配項或位置 criterium1 為 NULL，我需要在 criterium2 上進行匹配。

在上面的範例中，預期的結果集將是：

resultset
positionId  productId
1            1 (no match on criterium1 since NULL, match on criterium2 )
2            2 (match on criterium1)
3            2 (no match on criterium1, match on criterium2 )
4            1 (match on criterium1, criterium2 match is disregarded)

我只想匹配positions.criterium1和。products.criterium1``positions.criterium2``products.criterium2

假設 pro1，criterium1 應該是 AA1，這會產生您正在尋找的結果：

SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
  , pro.criterium1, pro.criterium2 
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1 
  OR (pos.criterium2 = pro.criterium2
  AND pos.id NOT IN (
     SELECT posx.id FROM Positions posx
     JOIN Products prox ON posx.criterium1 = prox.criterium1
  ))
ORDER BY 1;

測試查詢（僅在 Oracle 中測試）：

WITH Products AS (
  SELECT 1 id, 'AA1' criterium1, '910' criterium2 FROM dual UNION ALL
  SELECT 2 id, '106'  criterium1, 'BB2' criterium2 FROM dual UNION ALL
  SELECT 3 id, 'AB1'  criterium1, 'XXY' criterium2 FROM dual
  ),
  Positions AS (
  SELECT 1 id, NULL  criterium1, '910' criterium2 FROM dual UNION ALL
  SELECT 2 id, '106' criterium1, 'CCC' criterium2 FROM dual UNION ALL 
  SELECT 3 id, 'XXX' criterium1, 'BB2' criterium2 FROM dual UNION ALL 
  SELECT 4 id, 'AA1' criterium1, 'XXY' criterium2 FROM dual UNION ALL 
  SELECT 5 id, NULL  criterium1, '123' criterium2 FROM dual    
  )   
SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
  , pro.criterium1, pro.criterium2 
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1 
  OR (pos.criterium2 = pro.criterium2
  AND pos.id NOT IN (
     SELECT posx.id FROM Positions posx
     JOIN Products prox ON posx.criterium1 = prox.criterium1
  ))
ORDER BY 1;

不確定我是否明白您的目標，但這可能會讓您走得更遠。（PostgreSQL）。

select * 
from positions pos 
inner join products pro 
on case when pos.criterium1 is not null then pos.criterium1 = pro.criterium1 
       when pos.criterium1 is null     then pos.criterium2 = pro.criterium2 
  end

引用自：https://dba.stackexchange.com/questions/9543