Início / dba / Perguntas / 256849
Accepted
MrVimes
Asked: 2020-01-08 04:11:55 +0800 CST

Junte-se a um valor 'padrão' somente quando não houver nenhum registro totalmente correspondente

  • 772

Eu preciso ser capaz de ingressar em uma linha 'padrão' em uma tabela (tabela direita) somente quando não houver registro correspondente para ambos os campos na tabela esquerda. Abaixo está o conteúdo bruto das tabelas

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


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

Neste exemplo, onde a corrida é 1, preciso escolher a primeira linha (comissão = 14), mas se a corrida for diferente de 1, preciso escolher a linha NULL que corresponde ao outro campo (comissão = 23)

O problema que estou tendo é que minha consulta escolhe o registro nulo e o registro correspondente ao ingressar, resultando em duplicação ...

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;

Isso resulta no abaixo

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

O valor total é o dobro do que deveria ser (e comissão incorreta) porque a junção criou duas linhas para a corrida 1 (uma para cada linha na tabela direita)

Idealmente, eu quero ser capaz de resolver esse problema inteiramente dentro da cláusula join, em vez de ter que adicionar cluds confusos à cláusula select, ou junções adicionais, ou visualizações, ou modificar righttable para criar uma linha para cada corrida, independentemente.

Eu tentei diferentes maneiras de construir a cláusula join, mas não consigo entender como dizer "Dê-me a linha com a corrida nula somente se não houver nenhuma linha que corresponda à corrida na tabela esquerda" usando uma junção sql.

Isso é possível?

sql-server-2005

1 respostas

  1. Best Answer

    Você deve ser capaz de obtê-lo usando uma junção OUTER APPLY mais 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

    retorna:

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

    mas você só precisa da primeira linha, que você pode obter usando um TOP 1 desta maneira:

    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<>fique aqui

    • 2

relate perguntas