mysql - 通过减去车辆读数来计算车辆里程

我有一张表格，其中定期存储车辆行驶里程，即车辆行驶里程。

The table:
----------
ID (primary key)
Vehicle_id
date_of_reading (datetime)
Mileage

在本月底之前，我想选择上个月的最后一个读数，然后从本月的最后一个读数中减去它，以得出所有车辆行驶的总公里数。

http://sqlfiddle.com/#!9/34b8b

表的结构

CREATE TABLE `tbl_readings` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `vehicle_id` int(11) NOT NULL,
 `km_driven` int(11) NOT NULL,
 `dt_of_reading` datetime NOT NULL,
  `dt_of_entry` datetime NOT NULL,
  `entry_user` varchar(50) NOT NULL,   PRIMARY KEY (`id`),
  KEY `vehicle_id` (`vehicle_id`),  CONSTRAINT `tbl_readings_ibfk_1`     FOREIGN KEY (`vehicle_id`) REFERENCES `table_vehicle_info` (`id`) ON DELETE     CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=394     DEFAULT CHARSET=latin1

该表几乎每天都会发布车辆仪表读数。到月底，这些读数将用于计算车辆行驶的公里数。 Km的编号必须通过获取上个月的最后一个读数，然后从当前月份的最后一个读数中减去来得出。

我希望现在更加清楚。我意识到我的问题太模棱两可了。

最佳答案

巨大的免责声明：这不附带任何保证，但是如果我了解您的问题并且在某个地方我没有记错，请考虑以下内容。

你的桌子被

CREATE TABLE `foo` (`id` INTEGER, `date` DATE, `mileage` INT);

一种可能的方法是，首先使用上个月的最新阅读日期创建一个视图（如果您愿意，可以将其转换为另一个嵌套的SELECT）：

CREATE VIEW `past_month_readings`
AS (SELECT `id`,
    max(`date`) AS `latestreading`
    FROM `foo`
    WHERE MONTH(`date`) = MONTH(NOW())-1
    GROUP BY `id`);

其次是

SELECT `currentmileages`.`id`, `currentmileage`-`previousmileage` FROM
 (SELECT `foo`.`id`, `mileage` AS `currentmileage`
  FROM `foo`
  JOIN (SELECT `id`, MAX(`date`) AS `latestreading` FROM `foo` GROUP BY `id`)
  AS `baz`
  ON `foo`.`id` = `baz`.`id`
  AND `foo`.`date` = `baz`.`latestreading`
 )
AS `currentmileages`
JOIN
 (SELECT `foo`.`id`, `mileage` AS `previousmileage`
  FROM `foo`
  JOIN `past_month_readings`
  ON `foo`.`id`=`past_month_readings`.`id`
  AND `foo`.`date` = `past_month_readings`.`latestreading`)
AS `previousmileages`
ON
`currentmileages`.`id` = `previousmileages`.`id`

对于

+------+------------+---------+
| id   | date       | mileage |
+------+------------+---------+
|    1 | 2015-06-15 |    1234 |
|    1 | 2015-07-15 |    1444 |
|    1 | 2015-07-25 |    2000 |
|    2 | 2015-06-01 |     100 |
|    2 | 2015-06-20 |     200 |
|    2 | 2015-07-20 |     300 |
+------+------------+---------+

这导致

+------+------------------------------------+
| id   | `currentmileage`-`previousmileage` |
+------+------------------------------------+
|    1 |                                766 |
|    2 |                                100 |
+------+------------------------------------+