查询结果的行号(按列分组) [英] Row number for query results grouped by a column

问题描述

我有一个包含以下各列的表: id | fk_id | rcv_date

I have a table that has the following columns: id | fk_id | rcv_date

可能有多个具有共同fk_id的记录，该fk_id表示相关表中的外键ID.

There may be multiple records with a common fk_id, which represents a foreign key id in a related table.

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

I need to create a query that will assign a row number to each record, grouped by fk_id, sorted by rcv_date.

我最初是从以下查询开始的，它对于排序和分配行号非常有效:

I originally began with the following query, which works quite well for sorting and assigning row numbers:

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

但是-行计数和排序是在整个数据集中进行的.我需要计数在一个普通的fk_id内.例如，将返回以下示例数据(第一列表示行数/顺序):

However -- the row count and sorting is done across the entire dataset. I need the counting to be within a common fk_id. For example, the following sample data would return (the first column represents the row count/ordinality):

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分组"之内.

The middle column represents the fk_id. As you can see, the sorting and row count is within the fk_id "grouping."

更新 我有一个查询似乎正在运行，但是想要一些关于是否可以改进的输入:

UPDATE I have a query that seems to be working, but would like some input as to whether it can be improved:

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.

So what this does is order by fk_id and then rcv_date -- which essentially handles my grouping. Then I use a second variable to compare the fk_id in the previous record with the current record: if it's the same, we increment the row; if different, we reset to 1.

我的真实数据测试似乎正在运行.我怀疑这是一个效率很低的查询-因此，如果有人有改进的想法，或者看到可能的缺陷，我很想听听.

My tests with real data appear to be working. I suspect it's a pretty inefficient query though -- so if anyone has ideas for improving it, or see possible flaws, I would love to hear.

推荐答案

这应该非常简单.

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进行排序以确保所有外键都在一起(如果它们不在表中呢?)，然后我按[c1>]进行了您的首选排序.该查询检查fk_id中是否有更改，如果有，则将行号变量设置为1，否则该变量递增.它以case语句处理.请注意，@fk:=fk_id是在进行大小写检查之后完成的，否则会影响行号.

I ordered by fk_id first to ensure all your foreign keys come together (what if they are not really in the table?), then I did your preferred ordering, ie by rcv_date. The query checks for a change in fk_id and if there is one, then row number variable is set to 1, or else the variable is incremented. Its handled in case statement. Notice that @fk:=fk_id is done after the case checking else it will affect the row number.

编辑:刚注意到您自己的解决方案与我最终得到的解决方案相同.荣誉！ :)

Just noticed your own solution which happened to be the same as I ended up with. Kudos! :)

这篇关于查询结果的行号(按列分组)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！