时间戳

首页 > 解决方案 > 减去行以显示MySql中的差异

mysql - 减去行以显示MySql中的差异

问题描述

我创建了一个表格，在其中记录每天的计时表读数。我需要从一天到另一天减去总仪表读数以显示每日运行时间。

这是一个 DB fiddle 什么对一行有效：db fiddle

我的表如下所示：

        INSERT INTO `cuidadrun` (`cuidad_id`, `date`, `bc100a`, `bc100b`, `bc100c`, `bc100d`, `bc100e`) VALUES
    (1, '2018-10-18', 500, 500, 500, 500, 500),
    (2, '2018-10-19', 518, 520, 520, 520, 520),
    (3, '2018-10-20', 539, 540, 540, 540, 540),
    (4, '2018-10-21', 558, 560, 560, 560, 560);
    COMMIT;

---

**Query #1**

    SELECT date,
    COALESCE(m1.bc100a - (SELECT m2.bc100a FROM cuidadrun m2 WHERE m2.cuidad_id = m1.cuidad_id - 1), 0) AS diffBC100a 
    FROM cuidadrun m1;

结果：

| date       | diffBC100a |
| ---------- | ---------- |
| 2018-10-18 | 0          |
| 2018-10-19 | 18         |
| 2018-10-20 | 21         |
| 2018-10-21 | 19         |

我的目标是在下表中显示每个仪表的每日小时数。

| date       | diffBC100a | diffBC100b | diffBC100c | diffBC100d | diffBC100e |
| ---------- | ---------- | ---------- | ---------- | ---------- | ---------- |
| 2018-10-18 | 0          | 0          | 0          | 0          | 0          |
| 2018-10-19 | 18         | 20         | 20         | 20         | 20         |
| 2018-10-20 | 21         | 20         | 20         | 20         | 20         |
| 2018-10-21 | 19         | 20         | 20         | 20         | 20         |

我不知道我怎么能做到这一点。

标签： mysql

解决方案

id而不是子查询，我会对前一个值行执行“Self-Left-Join” 。

SELECT m1.date,
       COALESCE(m1.bc100a - m2.bc100a, 0) AS diffBC100a, 
       COALESCE(m1.bc100b - m2.bc100b, 0) AS diffBC100b, 
       COALESCE(m1.bc100c - m2.bc100c, 0) AS diffBC100c, 
       COALESCE(m1.bc100d - m2.bc100d, 0) AS diffBC100d, 
       COALESCE(m1.bc100e - m2.bc100e, 0) AS diffBC100e
FROM cuidadrun AS m1 
LEFT JOIN cuidadrun AS m2
  ON m2.cuidad_id = m1.cuidad_id - 1 
ORDER BY m1.date

结果

| date       | diffBC100a | diffBC100b | diffBC100c | diffBC100d | diffBC100e |
| ---------- | ---------- | ---------- | ---------- | ---------- | ---------- |
| 2018-10-18 | 0          | 0          | 0          | 0          | 0          |
| 2018-10-19 | 18         | 20         | 20         | 20         | 20         |
| 2018-10-20 | 21         | 20         | 20         | 20         | 20         |
| 2018-10-21 | 19         | 20         | 20         | 20         | 20         |

在 DB Fiddle 上查看

推荐阅读