mysql - 按列分组的查询结果的行号

我有一个包含以下列的表:
身份证 | fk_id |接收日期

可能有多个记录具有共同的 fk_id，它代表相关表中的外键 id。

我需要创建一个查询，该查询将为每条记录分配一个行号，按 fk_id 分组，按 rcv_date 排序。

我最初从以下查询开始，它非常适合排序和分配行号:

SELECT @row:=@row +1 AS ordinality, c.fk_id, rcv_date
FROM (SELECT @row:=0) r, mytable c
ORDER BY rcv_date

但是——行计数和排序是在整个数据集中完成的。我需要计数在一个共同的 fk_id 内。例如，将返回以下示例数据(第一列表示行数/顺序):

1 | 5 | 2011-10-01
2 | 5 | 2011-10-14
3 | 5 | 2011-11-02
4 | 5 | 2011-12-17
1 | 8 | 2011-09-03
2 | 8 | 2011-11-12
1 | 9 | 2011-10-08
2 | 9 | 2011-10-10
3 | 9 | 2011-11-19

中间一列代表 fk_id。如您所见，排序和行数在 fk_id “分组”内。

更新
我有一个似乎有效的查询，但想要一些关于它是否可以改进的意见:

SELECT IF(@last = c.fk_id, @row:=@row +1, @row:=1) AS ordinality, @last:=c.fk_id, c.fk_id, rcv_date
FROM (SELECT @row:=0) r, (SELECT @last:=0) l, mytable c
ORDER BY c.fk_id, rcv_date

所以它的作用是按 fk_id 和 rcv_date 排序——它基本上处理我的分组。然后我使用第二个变量将前一条记录中的 fk_id 与当前记录进行比较:如果相同，我们增加该行；如果不同，我们重置为 1。

我对真实数据的测试似乎有效。我怀疑这是一个非常低效的查询 - 所以如果有人有改进它的想法，或者看到可能的缺陷，我很乐意听到。

最佳答案

这应该很简单。

SELECT (CASE WHEN @fk <> fk_id THEN @row:=1 ELSE @row:=@row + 1 END) AS ordinality,
       @fk:=fk_id, rcv_date
FROM   (SELECT @row:=0) AS r,
       (SELECT @fk:=0) AS f,
       (SELECT fk_id, rcv_date FROM files ORDER BY fk_id, rcv_date) AS t

我首先通过 fk_id 订购以确保您所有的外键都放在一起(如果它们不在表中怎么办？)，然后我做了您喜欢的订购，即通过 rcv_date 。查询检查 fk_id 的变化，如果有变化，则行号变量设置为 1，否则变量递增。它在 case 语句中处理。请注意 @fk:=fk_id 是在大小写检查之后完成的，否则它会影响行号。

编辑: 刚刚注意到您自己的解决方案，恰好与我最终得到的解决方案相同。荣誉! :)

关于mysql - 按列分组的查询结果的行号，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13216427/