时间戳

首页 > 解决方案 > 在mysql中计算总成本

php - 在mysql中计算总成本

问题描述

我有一个数据库表燃料，其中有一个 fID 字段（主要，自动增量）、vID（用于车辆 ID）、体积（以升为单位的燃料量）、价格（用于每升燃料的成本）、仪表（用于当前加注日期的车辆里程表），日期（车辆加注日期），vendorID（燃料供应商或车辆加注地点），注释（车辆或燃料的任何附加注释）和类型（燃料等级）。

我想进行一个查询，通过将体积（购买的升数）乘以价格（每升成本）然后添加特定车辆的所有燃料条目的总数，然后最后添加成本来计算每次填充的燃料成本表中的所有燃料条目（fID 是唯一的，但 vID 会重复（因为车辆会不时加满）。

这就是我现在正在运行的，但它只返回每次填充的成本（而不是添加其他填充）。

Select
   date_format(f1.date, '%y-%m-%y %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.fID,
   f1.volume,
   f1.volume * f1.price as cost
from
    fuel f1
left outer join
    fuel f2
On
  f2.date = (
                     select
                            max(date) 
                      from
                            fuel
                      where 
                             fuel.vID = f1.vID
                       and
                             fuel.date < f1.date
                  ) 
    where
          f1.ID = ? 
    Order by f1.date

任何帮助将不胜感激。

标签： phpmysqlsql

解决方案

您只需对查询进行细微更改，删除WHERE子句并添加GROUP BY：

SELECT f1.vid,
  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)
GROUP BY f1.vid, f1.date WITH ROLLUP

对于您上一个问题的示例数据，这给出了以下结果。您可以在列中检测每辆车的汇总行，null在列中检测date总体汇总行。nullvid

vid     date                mileage     distance    volume  cost
26      18-05-27 05:57:00   4500        0           25      18750
26      18-05-27 05:58:00   6000        1500        20      15000
26      (null)              6000        1500        20      33750
27      18-05-27 04:58:00   1200        0           15      18000
27      18-05-27 05:50:00   2000        800         5       5000
27      (null)              2000        800         5       23000
28      18-05-27 05:53:00   5000        0           15      12000
28      (null)              5000        0           15      12000
(null)  (null)              5000        0           15      68750

演示

如果您对查看每辆车的各个条目不感兴趣，则可以从GROUP BY（以及从选择中删除特定于日期的变量）中删除日期。此查询还将对距离和体积求和：

SELECT f1.vid,
  SUM(CASE WHEN f2.meter IS NULL THEN 0
       ELSE f1.meter - f2.meter
       END) AS distance,
  SUM(f1.volume) as 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)
GROUP BY f1.vid WITH ROLLUP

输出（摘要行有null）vid：

vid     distance    volume  cost
26      1500        45      33750
27      800         20      23000
28      0           15      12000
(null)  2300        80      68750

演示

推荐阅读