mysql enum可以order_在MySQL中按顺序枚举记录，按日期分组

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.

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.