Name | Date | Hours | Count |
---|---|---|---|
Mills | 2022-07-17 | 23 | 12 |
Mills | 2022-07-18 | 00 | 15 |
Mills | 2022-07-18 | 01 | 20 |
Mills | 2022-07-18 | 02 | 22 |
Mills | 2022-07-18 | 03 | 25 |
Mills | 2022-07-18 | 04 | 20 |
Mills | 2022-07-18 | 05 | 22 |
Mills | 2022-07-18 | 06 | 25 |
MIKE | 2022-07-18 | 00 | 15 |
MIKE | 2022-07-18 | 01 | 20 |
MIKE | 2022-07-18 | 02 | 22 |
MIKE | 2022-07-18 | 03 | 25 |
MIKE | 2022-07-18 | 04 | 20 |
我当前的输入表存储了一天中每小时连续记录的计数信息.我需要提取连续计数的值差,但由于我被迫使用MySQL 5.7,所以我很难做到这一点.
我已将查询编写如下:
SET @cnt := 0;
SELECT Name, Date, Hours, Count, (@cnt := @cnt - Count) AS DiffCount
FROM Hourly
ORDER BY Date;
这并没有给出确切的结果.
我希望有以下输出:
Name | Date | Hours | Count | Diff |
---|---|---|---|---|
Mills | 2022-07-17 | 23 | 12 | 0 |
Mills | 2022-07-18 | 00 | 15 | 3 |
Mills | 2022-07-18 | 01 | 20 | 5 |
Mills | 2022-07-18 | 02 | 22 | 2 |
Mills | 2022-07-18 | 03 | 25 | 3 |
Mills | 2022-07-18 | 04 | 20 | 5 |
Mills | 2022-07-18 | 05 | 22 | 2 |
Mills | 2022-07-18 | 06 | 25 | 3 |
MIKE | 2022-07-18 | 00 | 15 | 0 |
MIKE | 2022-07-18 | 01 | 20 | 5 |
MIKE | 2022-07-18 | 02 | 22 | 2 |
MIKE | 2022-07-18 | 03 | 25 | 3 |
MIKE | 2022-07-18 | 04 | 20 | 5 |
MIKE | 2022-07-18 | 05 | 22 | 2 |
MIKE | 2022-07-18 | 06 | 25 | 3 |
请指出我遗漏了什么.