我有两个表 RSLTS 和 CONTACTS：
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
我正在尝试创建一个报告来显示每个CONTACT记录 （C_ID), the RANK() of RES_ID (by 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
到目前为止，我尝试了这个，但最后一行返回 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: SQL小提琴 http://sqlfiddle.com/#!3/6ef2f/1
因为排名根本不取决于联系人
排名_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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
