mysql - 行总和以及总计和行之间的差

Question

在 sql 选择查询之后，我得到的数组是写下来的数组，我想向这个数组添加一个 sum(values) A​​S Total_Values 列和一个 sum(values) - values AS Diff_Values 列，我尝试了多种方法，但我没有找到正确的方法

我当前查询的结果是这样的:

Car_Number  Start_time  Code_nr Values  Total_Values
BH-07-EWR   08:59:00    C00425  7000    44400
BH-07-EWR   10:29:00    C00149  8500    44400
BH-07-EWR   14:27:00    C01075  9200    44400
BH-07-EWR   15:07:00    C00305  9800    44400
BH-07-EWR   16:08:00    C02572  9900    44400

我希望它是:

Car_Number  Start_time  Code_nr Values  Total_Values    Diff_Values
BH-07-EWR   08:59:00    C00425  7000    44400           44400
BH-07-EWR   10:29:00    C00149  8500    44400           37400
BH-07-EWR   14:27:00    C01075  9200    44400           28900
BH-07-EWR   15:07:00    C00305  9800    44400           19700
BH-07-EWR   16:08:00    C02572  9900    44400           9900

我怎样才能做到这一点？事情可能看起来很简单，但根据我的经验来说有点复杂。谢谢

更新:

示例 SQL:

SELECT *, SUM(MINUTE(rest_time)) AS Times FROM routes as routes 
LEFT JOIN sales ON (routes.departure_poi_code = sales.client_code 
                AND routes.departure_date = sales.date) 
WHERE (routes.departure_poi_code LIKE 'C0%' 
       OR routes.departure_poi_code LIKE 'MP%') 
      AND routes.car_no = 'BH-07-EWR' 
      AND routes.departure_date = '2011-10-14' 
GROUP BY routes.departure_address, reports.routes.departure_poi_code, reports.routes.car_no, reports.routes.departure_date 
ORDER BY routes.car_no LIMIT 500000 ; 

当然，查询会返回更多显示的列。

Answer 1

假设您的表名为 TBL。

SELECT TBL.*, Total_Values, @R:=IF(@R=0, Total_Values, @R-TBL.`Values`) AS Diff_Values
  FROM TBL, ( SELECT @R:=0, SUM(`Values`) AS Total_Values FROM TBL ) x;