SQL RANK() 在连接表上的 PARTITION [英] SQL RANK() over PARTITION on joined tables
本文介绍了SQL RANK() 在连接表上的 PARTITION的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表 RSLTS 和 CONTACTS:
I have two tables RSLTS and 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
) 显示 RANK()
的 RANK()
代码>RES_ID(按SCORE
)在其组内的RSLTS 表中(QRY_ID
).使用上面的数据,它看起来像这样:
I'm trying to create a report that would show, for each CONTACT record (C_ID
), the RANK()
of RES_ID
(by SCORE
) in the RSLTS table within its group (QRY_ID
). Using the data above, it would look like this:
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 这也是错误的)
So far, I tried this but it returns Rank = 1 for the last row (and rank = 2 for the second which is also wrong)
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
推荐答案
因为排名完全不依赖于联系人
As the rank doesn't depend at all from the contacts
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.*
,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
这篇关于SQL RANK() 在连接表上的 PARTITION的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文