僅當沒有完全匹配的記錄時才加入“預設”值

僅當左表中的兩個欄位都沒有匹配的記錄時，我才需要能夠加入表（右表）中的“預設”行。以下是表格的原始內容

#lefttable
--------------------
value  program  race
100    gold     1
100    gold     4
100    gold     5


#righttable
-------------------------
commission  program  race
14          GOLD     1
23          GOLD     NULL

在此範例中，比賽為 1 我需要選擇第一行（佣金 = 14），但如果比賽不是 1，我需要選擇與其他欄位匹配的 NULL 行（佣金 = 23）

我遇到的問題是我的查詢在加入時選擇了空記錄和匹配記錄，導致重複……

SELECT #lefttable.race,
      totalvalue = SUM(value),
      commission = SUM(value * (commission * 0.01))
 FROM #lefttable
 LEFT JOIN #righttable  ON #lefttable.program = #righttable.program
                       AND #lefttable.race = ISNULL(#righttable.race, #lefttable.race)
GROUP BY #lefttable.race;

這導致以下結果

--------------------
race  totalvalue  commission
1     200         37.00
3     100         23.00
4     100         23.00

總價值是應有的兩倍（並且佣金不正確），因為連接為比賽 1 創建了兩行（右表中的每一行都有一個）

理想情況下，我希望能夠完全在 join 子句中解決這個問題，而不必在 select 子句中添加雜亂的 cluds，或者額外的連接或視圖，或者修改 righttable 以無論如何為每場比賽創建一行。

我嘗試了不同的方法來建構連接子句，但我不能完全理解如何使用 sql 連接說“只有在沒有與 lefttable 中的比賽匹配的行時才給我帶有空比賽的行”。

這可能嗎？

您應該能夠通過使用 OUTER APPLY join 加上 TOP 1 來獲得它

select
   *
from 
   righttable r 
where 
   (r.race=1 and r.program = 'gold')
   or
   r.race is null
order by
   case when r.race is null then 1 else 0 end

返回：

commission | program | race
---------: | :------ | ---:
       14 | gold    |    1
       23 | gold    | null

但您只需要第一行，您可以通過以下方式使用 TOP 1 獲得：

select
   l.race, 
   totalvalue = sum(value), 
   commission = SUM(value * (commission * 0.01))
from 
   lefttable l
outer apply
   (select top 1 
       commission 
    from 
        righttable r 
    where 
        (r.race=l.race and r.program = l.program)
        or
        r.race is null
    order by
        case when r.race is null then 1 else 0 end) r
group by
   l.race;

race | totalvalue | commission
---: | ---------: | :---------
  1 |        100 | 14.00     
  4 |        100 | 23.00     
  5 |        100 | 23.00     

db<>在這裡擺弄

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