Início / dba / Perguntas / 129340
Accepted
Lambo Jayapalan
Asked: 2016-02-16 13:14:56 +0800 CST

Consulta para selecionar o valor máximo na junção

  • 772


Eu tenho uma tabela de usuários:

|Username|UserType|Points|
|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

e Níveis

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

E estou procurando uma consulta para obter o nível de cada usuário. Algo na linha de:

SELECT *
FROM Users U
INNER JOIN (
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

De modo que os resultados seriam:

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

Alguém tem alguma idéia ou sugestão sobre como eu poderia fazer isso sem recorrer a cursores?

sql-server sql-server-2012

4 respostas

  1. Best Answer

    Sua consulta existente está próxima de algo que você poderia usar, mas você pode obter o resultado facilmente fazendo algumas alterações. Ao alterar sua consulta para usar o APPLYoperador e implementar o CROSS APPLY. Isso retornará a linha que atende aos seus requisitos. Aqui está uma versão que você pode usar:

    SELECT 
  u.Username, 
  u.UserType,
  u.Points,
  lv.Level
FROM Users u
CROSS APPLY
(
  SELECT TOP 1 Level
  FROM Levels l
  WHERE u.UserType = l.UserType
     and l.MinPoints < u.Points
  ORDER BY l.MinPoints desc
) lv;

    Aqui está um SQL Fiddle com uma demonstração . Isso produz um resultado:

    | Username | UserType | Points |  Level |
|----------|----------|--------|--------|
|     John |        A |    250 | Silver |
|     Mary |        A |    150 | Bronze |
|     Anna |        B |    600 | Bronze |
    • 18

  2. A solução a seguir usa uma expressão de tabela comum que verifica a Levelstabela uma vez. Nesta varredura, o nível de pontos "próximo" é encontrado usando a LEAD()função window, então você tem MinPoints(da linha) e MaxPoints(o próximo MinPointspara o atual UserType).

    Depois disso, você pode simplesmente juntar a expressão de tabela comum, lvls, on UserTypee o intervalo MinPoints/ MaxPoints, assim:

    WITH lvls AS (
    SELECT UserType, MinPoints, [Level],
           LEAD(MinPoints, 1, 99999) OVER (
               PARTITION BY UserType
               ORDER BY MinPoints) AS MaxPoints
    FROM Levels)

SELECT U.*, L.[Level]
FROM Users AS U
INNER JOIN lvls AS L ON
    U.UserType=L.UserType AND
    L.MinPoints<=U.Points AND
    L.MaxPoints> U.Points;

    A vantagem de usar a função de janela é que você elimina todos os tipos de soluções recursivas e melhora drasticamente o desempenho. Para melhor desempenho, você usaria o seguinte índice na Levelstabela:

    CREATE UNIQUE INDEX ... ON Levels (UserType, MinPoints) INCLUDE ([Level]);
    • 3

  3. Por que não fazer isso usando apenas as operações rudimentares, INNER JOIN, GROUP BY e MAX:

    SELECT   U1.*,
         L1.Level

FROM     Users AS U1

         INNER JOIN
         (
          SELECT   U2.Username,
                   MAX(L2.MinPoints) AS QualifyingMinPoints
          FROM     Users AS U2
                   INNER JOIN
                   Levels AS L2
                   ON U2.UserType = L2.UserType
          WHERE    L2.MinPoints <= U2.Points
          GROUP BY U2.Username
         ) AS Q
         ON U1.Username = Q.Username

         INNER JOIN
         Levels AS L1
         ON Q.QualifyingMinPoints = L1.MinPoints
            AND U1.UserType = L1.UserType
;
    • 2

  4. Eu acho que você pode usar um INNER JOIN-como um problema de desempenho que você também pode usar LEFT JOIN- com ROW_NUMBER()uma função como esta:

    SELECT 
    Username, UserType, Points, Level
FROM (
    SELECT u.*, l.Level,
      ROW_NUMBER() OVER (PARTITION BY u.Username ORDER BY l.MinPoints DESC) seq
    FROM 
        Users u INNER JOIN
        Levels l ON u.UserType = l.UserType AND u.Points >= l.MinPoints
    ) dt
WHERE
    seq = 1;

    Demonstração do SQL Fiddle

    • 2

relate perguntas