mysql - 将每天递增的数字添加到表中

Question

我有一个带有日期戳的交易表。我想添加一列来对记录进行编号，但它们必须每天重新启动。这可能吗？

示例:

+------------------+--------------+
| Date             | Data         |
+------------------+--------------+
| 19-10-2017 10:18 | Transaction1 |
| 19-10-2017 12:18 | Transaction2 |
| 19-10-2017 13:23 | Transaction3 |
| 20-10-2017 11:18 | Transaction4 |
| 20-10-2017 14:18 | Transaction5 |
| 21-10-2017 09:18 | Transaction6 |
| 21-10-2017 10:18 | Transaction7 |
| 21-10-2017 11:18 | Transaction8 |
| 21-10-2017 12:18 | Transaction9 |
+------------------+---------+----+

我想要这个:

+------+------------------+--------------+
| Nr   | Date             | Data         |
+------+------------------+--------------+
|    1 | 19-10-2017 10:18 | Transaction1 |
|    2 | 19-10-2017 12:18 | Transaction2 |
|    3 | 19-10-2017 13:23 | Transaction3 |
|    1 | 20-10-2017 11:18 | Transaction4 |
|    2 | 20-10-2017 14:18 | Transaction5 |
|    1 | 21-10-2017 09:18 | Transaction6 |
|    2 | 21-10-2017 10:18 | Transaction7 |
|    3 | 21-10-2017 11:18 | Transaction8 |
|    4 | 21-10-2017 12:18 | Transaction9 |
+------+------------------+--------------+

Answer 1

您可以尝试以下解决方案:

SELECT 
    @row_number:=CASE WHEN DATE(@date_value)=DATE(`Date`) THEN @row_number + 1 ELSE 1 END AS row_number,
    @date_value:=`Date` AS `Date`
FROM test, (SELECT @row_number:=0, @date_value:=DATE(NULL)) AS t
ORDER BY `Date`, row_number