사용자 테이블이 있습니다.
|Username|UserType|Points|
|John |A |250 |
|Mary |A |150 |
|Anna |B |600 |
그리고 수준
|UserType|MinPoints|Level |
|A |100 |Bronze |
|A |200 |Silver |
|A |300 |Gold |
|B |500 |Bronze |
그리고 각 사용자의 수준을 얻는 쿼리를 찾고 있습니다. 다음과 같은 내용이 있습니다.
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
결과는 다음과 같습니다.
|Username|UserType|Points|Level |
|John |A |250 |Silver |
|Mary |A |150 |Bronze |
|Anna |B |600 |Bronze |
커서를 사용하지 않고 어떻게 할 수 있는지에 대한 아이디어 나 제안이 있습니까?
답변
기존 쿼리는 사용할 수있는 것과 비슷하지만 약간만 변경하면 쉽게 결과를 얻을 수 있습니다. APPLY연산자 를 사용하도록 쿼리를 변경 하고 구현 CROSS APPLY합니다. 그러면 요구 사항에 맞는 행이 반환됩니다. 사용할 수있는 버전은 다음과 같습니다.
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;
다음 은 데모 가 포함 된 SQL Fiddle입니다 . 결과는 다음과 같습니다.
| Username | UserType | Points | Level |
|----------|----------|--------|--------|
| John | A | 250 | Silver |
| Mary | A | 150 | Bronze |
| Anna | B | 600 | Bronze |
답변
다음 솔루션은 Levels테이블을 한 번 스캔하는 공통 테이블 표현식을 사용합니다 . 이 스캔에서 “다음”포인트 레벨은 LEAD()윈도우 기능을 사용하여 발견 되므로 MinPoints(행에서) 및 MaxPoints( MinPoints현재 에서 다음 ) 을 갖습니다 UserType.
그런 다음 공통 테이블 표현식 lvls, on UserType및 MinPoints/ MaxPointsrange를 다음과 같이 간단히 결합 할 수 있습니다 .
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;
창 기능을 사용하면 모든 종류의 재귀 솔루션을 제거하고 성능을 크게 향상시킬 수 있다는 이점이 있습니다. 최상의 성능을 위해 Levels테이블 에서 다음 색인을 사용하십시오 .
CREATE UNIQUE INDEX ... ON Levels (UserType, MinPoints) INCLUDE ([Level]);답변
기초 작업 인 INNER JOIN, GROUP BY 및 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
;
답변
다음과 같은 기능을 INNER JOIN사용하여 성능 문제로 사용할 수도 있다고 생각합니다 .LEFT JOINROW_NUMBER()
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;