SQL RANK() 在连接表上的 PARTITION
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13962950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL RANK() over PARTITION on joined tables
提问by greener
I have two tables RSLTS and CONTACTS:
我有两个表 RSLTS 和 CONTACTS：
RSLTS
RSLTS
QRY_ID | RES_ID | SCORE
-----------------------------
A | 1 | 15
A | 2 | 32
A | 3 | 29
C | 7 | 61
C | 9 | 30
CONTACTS
联系方式
C_ID | QRY_ID | RES_ID
----------------------------
1 | A | 2
2 | A | 1
3 | C | 9
I'm trying to create a report that would show, for each CONTACTrecord (C_ID), the RANK()of RES_ID(by SCORE) in the RSLTStable within its group (QRY_ID). Using the data above, it would look like this:
我试图创建一份报告，显示每个联系人记录（C_ID）时，RANK()中RES_ID（通过SCORE在）RSLTS其组内表（QRY_ID）。使用上面的数据，它看起来像这样：
C_ID | QRY_ID | RES_ID | SCORE | Rank
-----------------------------------------------
1 | A | 2 | 32 | 1
2 | A | 1 | 15 | 3
3 | C | 9 | 30 | 2
So far, I tried this but it returns Rank = 1 for the last row (and rank = 2 for the second which is also wrong)
到目前为止，我尝试过这个，但它为最后一行返回 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
UPDATE: SQLFiddle
更新：SQLFiddle
回答by Johan Buret
As the rank doesn't depend at all from the contacts
由于排名完全不依赖于联系人
RANKED_RSLTS
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
Thus :
因此 ：
SELECT
C.*
,R.SCORE
,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
回答by sai wang
SELECT a.C_ID,a.QRY_ID,a.RES_ID,b.SCORE,ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [RANK]
FROM CONTACTS a JOIN RSLTS b ON a.QRY_ID=b.QRY_ID AND a.RES_ID=b.RES_ID
ORDER BY a.C_ID
