MySQL 行变量乱序

Question

我有这 2 个 MySQL 表:

DESCRIBE time;
+---------------+-------------------------------------+------+-----+---------+----------------+
| Field         | Type                                | Null | Key | Default | Extra          |
+---------------+-------------------------------------+------+-----+---------+----------------+
| id            | int(11)                             | NO   | PRI | NULL    | auto_increment |
| date          | date                                | NO   |     | NULL    |                |
+---------------+-------------------------------------+------+-----+---------+----------------+

DESCRIBE employee;
+---------+--------------------------------------+------+-----+---------+----------------+
| Field   | Type                                 | Null | Key | Default | Extra          |
+---------+--------------------------------------+------+-----+---------+----------------+
| id      | int(11)                              | NO   | PRI | NULL    | auto_increment |
| tid     | int(11)                              | NO   | MUL | NULL    |                |
| salary  | double                               | NO   |     | NULL    |                |
| age     | int(11)                              | NO   |     | NULL    |                |
+---------+--------------------------------------+------+-----+---------+----------------+

在下面的查询中，我尝试附加我的 [salary | ] 的行号。日期]表。当日期更改时，行号重置为 1。这是我的查询:

SELECT IF(@prev != d.date, @rownum := 1, @rownum := @rownum + 1) AS row, @prev := d.date, e.salary
FROM employee e, time d, (SELECT @rownum := 0, @prev := NULL) r
WHERE e.age >= 18 AND e.age <= 25 AND e.tid = d.id AND d.date >= '2002-01-01' AND d.date <= '2003-01-01'
ORDER BY date, salary;

我得到的结果如下第一个表所示。行号不正确。预期的行号显示在下面的第二个表中。

+------+-----------------+---------+
| row  | @prev := d.date | salary  |
+------+-----------------+---------+
|    6 | 2002-01-01      | 21823.2 |
|    2 | 2002-01-01      |   27279 |
|    9 | 2002-01-01      |   35073 |
|    8 | 2002-01-01      | 36631.8 |
|    4 | 2002-01-01      | 36631.8 |
|    7 | 2002-01-01      |   38970 |
|    5 | 2002-01-01      | 52219.8 |
|    3 | 2002-01-01      |  350730 |
|    1 | 2002-01-01      |  389700 |
|    7 | 2003-01-01      | 21823.2 |
|    2 | 2003-01-01      |   27279 |
|    4 | 2003-01-01      | 31565.7 |
|   10 | 2003-01-01      |   35073 |
|    3 | 2003-01-01      | 36242.1 |
|    9 | 2003-01-01      | 36631.8 |
|    5 | 2003-01-01      | 36631.8 |
|    8 | 2003-01-01      |   38970 |
|    6 | 2003-01-01      | 52219.8 |
|    1 | 2003-01-01      |  389700 |
+------+-----------------+---------+

我期望以下结果具有正确的行号。想法？

+------+-----------------+---------+
| row  | @prev := d.date | salary  |
+------+-----------------+---------+
|    1 | 2002-01-01      | 21823.2 |
|    2 | 2002-01-01      |   27279 |
|    3 | 2002-01-01      |   35073 |
|    4 | 2002-01-01      | 36631.8 |
|    5 | 2002-01-01      | 36631.8 |
|    6 | 2002-01-01      |   38970 |
|    7 | 2002-01-01      | 52219.8 |
|    8 | 2002-01-01      |  350730 |
|    9 | 2002-01-01      |  389700 |
|    1 | 2003-01-01      | 21823.2 |
|    2 | 2003-01-01      |   27279 |
|    3 | 2003-01-01      | 31565.7 |
|    4 | 2003-01-01      |   35073 |
|    5 | 2003-01-01      | 36242.1 |
|    6 | 2003-01-01      | 36631.8 |
|    7 | 2003-01-01      | 36631.8 |
|    8 | 2003-01-01      |   38970 |
|    9 | 2003-01-01      | 52219.8 |
|   10 | 2003-01-01      |  389700 |
+------+-----------------+---------+

Answer 1

SELECT IF(@prev != date, @rownum := 1, @rownum := @rownum + 1) AS row, 
       @prev := date, salary
  FROM  (SELECT d.date,e.salary
           FROM employee e, time d
          WHERE e.age >= 18 
            AND e.age <= 25 
            AND e.tid = d.id 
            AND d.date >= '2002-01-01' 
            AND d.date <= '2003-01-01'
       ORDER BY date, salary) a, (SELECT @rownum := 0, @prev := NULL) r;