我有一个包含以下各列的表: 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! :)
更多推荐
查询结果的行号(按列分组)
发布评论