枚举sql中具有多个相同值的行 [英] enumerating rows in sql with multiple same values

问题描述

我想枚举行，以使非唯一值获得相同的数字并且不会增加.所以我的数据应该是这样的:

I want to enumerate rows such that non-unique values get the same number and do not increase. So my data should look like:

id - term_seq_id - rn
---------------------
1    0944          1
1    0944          1
1    0962          2
2    1024          1
2    1054          2

我正在研究这个解决方案:

I was looking at this solution:

SQL:枚举每个组内返回的行

如果窗口函数中的 ORDER BY 语句中的值都是唯一的，则该方法有效.所以我从这个解决方案中有以下查询，但它不起作用:

Which works if the values are all unique in the ORDER BY statement in the window function. So I have the following query from this solution but it doesn't work:

SELECT  student_id_fk
, Term_Seq_Id
, ROW_NUMBER() OVER (PARTITION BY student_id_fk ORDER BY Term_Seq_Id ) AS rn
FROM Courses AS c

ORDER BY student_id_fk, Term_Seq_Id

然而，这无法提供正确的输出.相反，它确实:

However this fails to provide the correct output. Instead it does:

id - term_seq_id - rn
---------------------
1    0944          1
1    0944          2
1    0962          3
2    1024          1
2    1054          2

我不知所措，似乎没有任何方法可以说仅在 ​​term_seq_id 增加时才增加".我尝试按多行进行分区，但没有任何作用.

I am at a loss, there doesn't seem to be any way to say "only increment if term_seq_id increases". I tried partioning by multiple rows but that didn't do anything.

推荐答案

你在找这个吗:

SELECT  student_id_fk
, Term_Seq_Id
, DENSE_RANK() OVER (PARTITION BY student_id_fk ORDER BY Term_Seq_Id ) AS rn
FROM Courses AS c

ORDER BY student_id_fk, Term_Seq_Id

这篇关于枚举sql中具有多个相同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！