我有两个表RSLTS和CONTACTS：
RSLTS
QRY_ID | RES_ID | score ----------------------------- A | 1 | 15 A | 2 | 32 A | 3 | 29 C | 7 | 61 C | 9 | 30
联系
C_ID | QRY_ID | RES_ID ---------------------------- 1 | A | 2 2 | A | 1 3 | C | 9
我正在尝试创建一个报告,为每个CONTACT记录(C_ID)显示其组内的RSLTS表(QRY_ID)中RES_ID(通过score)的RANK().使用上面的数据,它看起来像这样：
C_ID | QRY_ID | RES_ID | score | Rank ----------------------------------------------- 1 | A | 2 | 32 | 1 2 | A | 1 | 15 | 3 3 | C | 9 | 30 | 2
到目前为止,我尝试了这个但是它返回了最后一行的Rank = 1(而第二行的rank = 2也是错误的)
SELECT
C.*,R.score,RANK() OVER (PARTITION BY R.QRY_ID ORDER BY R.score DESC)
FROM CONTACTS C LEFT JOIN RSLTS R
ON C.RES_ID = R.RES_ID
AND C.QRY_ID = R.QRY_ID
更新：SQLFiddle
解决方法
因为排名完全不依赖于联系人
RANKED_RSLTS
QRY_ID | RES_ID | score | RANK ------------------------------------- A | 1 | 15 | 3 A | 2 | 32 | 1 A | 3 | 29 | 2 C | 7 | 61 | 1 C | 9 | 30 | 2
因此：
SELECT
C.*,MYRANK
FROM CONTACTS C LEFT JOIN
(SELECT *,MYRANK = RANK() OVER (PARTITION BY QRY_ID ORDER BY score DESC)
FROM RSLTS) R
ON C.RES_ID = R.RES_ID
AND C.QRY_ID = R.QRY_ID
