Nick 的解决方案将起作用，但给定的联接条件是为每个联接运行的子查询。为了达到预期的效果，你也可以试试这个。

select f3.date, f3.meter, f3.cost, f3.volume, f3.price, min(f3.distance) as distance from (
  select  f1.date,
    f1.meter,
    case when f2.`meter` is NULL then 0
      else f1.meter - f2.meter end
      as distance,
    (f1.price * f1.volume) as cost,
    f1.`volume`,
    f1.`price`,
    f1.fID
  from fuel f1
  left join fuel f2 
  on f1.vid = f2.vid and f1.`date` > f2.date
  where f1.vid = 27
  group by f1.fID, distance
  order by date, distance asc) f3
group by f3.fID;

@尼克谢谢你的反馈。希望这个问题能解决。

此查询将为您提供所需的各个行。查询的工作方式是使用当前加油日期之前该车辆的最新加油日期将燃油连接到自身。如果没有先前的填充日期，则case表达式将生成距离为0的结果。

SELECT DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date, 
  f1.meter AS mileage,
  CASE WHEN f2.meter IS NULL THEN 0
       ELSE f1.meter - f2.meter
       END AS distance,
  f1.volume,
  f1.volume * f1.price AS cost
FROM fuel f1
LEFT JOIN fuel f2
  ON f2.date = (SELECT MAX(date) 
                FROM fuel 
                WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
WHERE f1.vID = 27
ORDER BY f1.date

输出：

date                mileage     distance    volume  cost
18-05-27 04:58:00   1200        0           15      18000
18-05-27 05:50:00   2000        800         5       5000

演示
如果您不想在php中对行求和，查询可以生成一个摘要行，只需对查询做一点小的更改（添加聚合函数和 GROUP BY WITH ROLLUP 条款）：

SELECT DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date, 
  f1.meter AS mileage,
  CASE WHEN f2.meter IS NULL THEN 0
       ELSE f1.meter - f2.meter
       END AS distance,
  f1.volume,
  SUM(f1.volume * f1.price) AS cost
FROM fuel f1
LEFT JOIN fuel f2
  ON f2.date = (SELECT MAX(date) 
                FROM fuel 
                WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
WHERE f1.vID = 27
GROUP BY f1.date WITH ROLLUP

输出：

date                mileage     distance    volume  cost
18-05-27 04:58:00   1200        0           15      18000
18-05-27 05:50:00   2000        800         5       5000
(null)              2000        800         5       23000

您可以通过date列是 null . 演示

ok sql也可以实现您想要的，但它通过存储过程更直观(我还将提到控制器和视图，即php和jquery也可以实现您想要的，但这样做要困难得多。在jquery情况下；mysql只需要返回vehcle记录集）

-- Proceuodo code
-- create a temporary table
-- set next_mileage = 0;
-- set pre_mileage = 0;
-- get a recordset of the desire vehicle
-- now loop through this recordset based on mileage
    -- if pre_mileage = 0 then pre_mileage = mileage(i);
    -- set next_mileage = mileage(i);
--        calculate distance (by subtracting next_mileage from previous_mileage) and other fields

DELIMITER @@
DROP PROCEDURE get_vehicle_info @@
CREATE PROCEDURE get_vehicle_info
    (IN vid INT)
BEGIN

    DECLARE v_vID BIGINT;
    DECLARE v_volume BIGINT;
    DECLARE v_price BIGINT;
    DECLARE v_meter BIGINT;
    DECLARE v_date datetime;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur1 CURSOR FOR SELECT vID, volume, price, meter, `date` FROM vehicle_records;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS vehcle_info;
    CREATE TEMPORARY TABLE vehcle_info (`Date` datetime, Mileage BIGINT, Distance BIGINT, Volume BIGINT, Cost BIGINT);
    SET @sqlStr = CONCAT('CREATE OR REPLACE view vehicle_records as SELECT vID, volume, price, meter, `date` FROM fuel WHERE vID = ', vid, ' ORDER BY meter');
    PREPARE stmt FROM @sqlStr;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt; 

    SET @pre_mileage = 0;
    SET @next_mileage = 0;

    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO v_vID, v_volume, v_price, v_meter, v_date;
        IF done THEN
            LEAVE read_loop;
        END IF;

        IF @pre_mileage = 0 THEN
            SET @pre_mileage = v_meter;
        END IF;
        SET @next_mileage = v_meter;

        INSERT INTO vehcle_info VALUES (v_date, v_meter, @next_mileage - @pre_mileage, v_volume, v_price * v_volume);
        SET @pre_mileage = v_meter;
    END LOOP;
    CLOSE cur1;
    SELECT * FROM vehcle_info;

    DROP VIEW IF EXISTS vehicle_records;
    DROP TEMPORARY TABLE vehcle_info;
END @@ 
DELIMITER ; 

CALL get_vehicle_info(27);