在MySQL中按顺序枚举记录，按日期分组 [英] Enumerate records sequentially, grouped and by date, in MySQL

问题描述

这似乎是一个这样简单的问题，我害怕我可能会被重复的问题锤击碎，但这里是我的：

  ID日期
 1 1/11/01 
 1 3/3/03 
 1 2/22/02 
 2 1/11/01 
 2 2/22/02 
  

我需要做的是根据日期枚举记录，并按 ID 分组！因此：

  ID日期编号
 1 1/11/01 1 
 1 3/3 / 03 3 
 1 2/22/02 2 
 2 1/11/01 1 
 2 2/22/02 2 
  

这非常类似于这个问题，但它不适合我。 这会很好，但它不是MySQL。

我尝试使用 group by ，但它不起作用，如

  SELECT ta。*，count（*）as Num 
 FROM temp_a ta 
 GROUP BY`ID` ORDER BY`ID`; 
  

自 GROUP BY 总是结果为一个值。

任何建议都非常感谢。 让我们假设表格如下：

  CREATE TABLE q43381823（id INT，dt DATE）; 
 INSERT INTO q43381823 VALUES 
（1，'2001-01-11'），
（1，'2003-03-03'），
（1，'2002- 02-22'），
（2，'2001-01-11'），
（2，'2002-02-22'）; 
  

然后，查询获得所需输出的方式之一是：

  SELECT q。*，
 CASE WHEN（
 IF（@id！= q.id，@等级：= 0，@rank：= @rank + 1）
）> = 1 THEN @rank 
 ELSE @rank：= 1 
 END，
 @id ：= q.id AS buffer_id 
 FROM q43381823 q 
 CROSS JOIN（
 SELECT @rank：= 0，
 @id：=（SELECT q2.id FROM q43381823 AS q2 ORDER BY q2.id LIMIT 1）
）x 
 ORDER BY q.id，q.dt 
  

输出：

  id | dt |等级| buffer_id 
 --------------------------------------------- ---- 
 1 | 2001-01-11 | 1 | 1 
 1 | 2002-02-22 | 2 | 1 
 1 | 2003-03-03 | 3 | 1 
 2 | 2001-01-11 | 1 | 2 
 2 | 2002-02-22 | 2 | 2 
  

您可以忽略 buffer_id 列 - 与结果无关，但需要重置 rank 。

SQL小提琴演示

---

解释：

• @id variable跟踪行中的每个id。在最初的迭代中，我们将它设置为可以在最终结果中获得的第一条记录的 id 。请参阅子查询 SELECT q2.id FROM q43381823 AS q2 ORDER BY q2.id LIMIT 1




• @rank 最初设置为 0 ，默认情况下会为结果集中的每个后续行增加。但是，当 id 发生变化时，我们将它重置为 1 。请在查询中查看 CASE - WHEN - ELSE 构造。

最后的输出是首先按 id ，然后按 dt 排序。这确保了对于同一个 id中的每个后续 dt 字段递增设置 @rank / code>，但每当新的 id 组开始出现在结果中时，它会重置为 1 设置。


This seems like such a simple question and I terrified that I might be bashed with the duplicate question hammer, but here's what I have:

ID  Date
1   1/11/01
1   3/3/03
1   2/22/02
2   1/11/01
2   2/22/02

All I need to do is enumerate the records, based on the date, and grouped by ID! As such:

ID  Date    Num
1   1/11/01 1
1   3/3/03  3
1   2/22/02 2
2   1/11/01 1
2   2/22/02 2

This is very similar to this question, but it's not working for me. This would be great but it's not MySQL.

I've tried to use group by but it doesn't work, as in

SELECT ta.*, count(*) as Num
FROM temp_a ta
GROUP BY `ID` ORDER BY `ID`;  

which clearly doesn't run since the GROUP BY always results to one value.

Any advice greatly appreciated.

解决方案

Let's assume the table to be as follows:

CREATE TABLE q43381823(id INT, dt DATE);
INSERT INTO q43381823 VALUES
(1, '2001-01-11'),
(1, '2003-03-03'),
(1, '2002-02-22'),
(2, '2001-01-11'),
(2, '2002-02-22');

Then, one of the ways in which the query to get the desired output could be written is:

SELECT q.*,  
    CASE WHEN (
            IF(@id != q.id, @rank := 0, @rank := @rank + 1)
         ) >=1 THEN @rank
         ELSE @rank := 1 
    END as rank,
    @id := q.id AS buffer_id
FROM q43381823 q
CROSS JOIN (
    SELECT  @rank:= 0, 
        @id  := (SELECT q2.id FROM q43381823 AS q2 ORDER BY q2.id LIMIT 1)
    ) x
ORDER BY q.id, q.dt

Output:

  id  |    dt        |   rank  |    buffer_id 
-------------------------------------------------    
  1   |  2001-01-11  |    1    |       1    
  1   |  2002-02-22  |    2    |       1
  1   |  2003-03-03  |    3    |       1
  2   |  2001-01-11  |    1    |       2
  2   |  2002-02-22  |    2    |       2

You may please ignore the buffer_id column from the output - it's irrelevant to the result, but required for the resetting of rank.

SQL Fiddle Demo

---

Explanation:

• @id variable keeps track of every id in the row, based on the sorted order of the output. In the initial iteration, we set it to id of the first record that may be obtained in the final result. See sub-query SELECT q2.id FROM q43381823 AS q2 ORDER BY q2.id LIMIT 1

• @rank is set to 0 initially and is by default incremented for every subsequent row in the result set. However, when the id changes, we reset it back to 1. Please see the CASE - WHEN - ELSE construct in the query for this.

• The final output is sorted first by id and then by dt. This ensures that @rank is set incrementally for every subsequent dt field within the same id, but gets reset to 1 whenever a new id group begins to show up in the result set.

这篇关于在MySQL中按顺序枚举记录，按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！