SQL RANK()在连接表上的PARTITION上

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!