主页 / dba / 问题 / 256849
Accepted
MrVimes
Asked: 2020-01-08 04:11:55 +0800 CST

仅当没有完全匹配的记录时才加入“默认”值

  • 772

仅当左表中的两个字段都没有匹配的记录时，我才需要能够加入表（右表）中的“默认”行。以下是表格的原始内容

#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 中的比赛匹配的行时才给我带有空比赛的行”。

这可能吗？

sql-server-2005

1 个回答

  1. Best Answer

    您应该能够通过使用 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<>在这里摆弄

    • 2

相关问题