Zapytanie, aby wybrać maksymalną wartość przy łączeniu

Mam tabelę użytkowników:

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

i poziomy

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

I szukam zapytania, aby uzyskać poziom dla każdego użytkownika. Coś w stylu:

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

Tak, aby wyniki były:

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

Czy ktoś ma jakieś pomysły lub sugestie, jak to zrobić bez uciekania się do kursorów?

Twoje istniejące zapytanie jest zbliżone do czegoś, czego możesz użyć, ale możesz łatwo uzyskać wynik, wprowadzając kilka zmian. Zmieniając zapytanie, aby korzystać z APPLYoperatora i wdrażając CROSS APPLY. Spowoduje to zwrócenie wiersza spełniającego Twoje wymagania. Oto wersja, której możesz użyć:

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;

Oto SQL Fiddle z wersją demonstracyjną . To daje wynik:

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

W poniższym rozwiązaniu zastosowano typowe wyrażenie tabeli, które skanuje Levelstabelę raz. W tym skanie poziom „następnych” punktów znajduje się za pomocą LEAD()funkcji okna, więc masz MinPoints(z rzędu) i MaxPoints(następny MinPointsdla bieżącego UserType).

Następnie możesz po prostu dołączyć do wspólnego wyrażenia tabelowego lvls, on UserTypei MinPoints/ MaxPointsrange, w następujący sposób:

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;

Zaletą korzystania z funkcji okna jest to, że eliminujesz wszelkiego rodzaju rozwiązania rekurencyjne i znacznie poprawiasz wydajność. Aby uzyskać najlepszą wydajność, należy użyć następującego indeksu w Levelstabeli:

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

Dlaczego nie zrobić tego przy użyciu podstawowych operacji, WEJŚCIA WEWNĘTRZNEGO, GROUP BY i 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
;

Myślę, że możesz użyć INNER JOIN- jako problemu z wydajnością, którego możesz również użyć - LEFT JOINz ROW_NUMBER()taką funkcją:

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;

Wersja demonstracyjna SQL Fiddle