gpt4 book ai didi

MySQL 表值与当前日期和上一个日期的差异

转载 作者:行者123 更新时间:2023-11-29 07:16:46 25 4
gpt4 key购买 nike

我有一些用于管理车辆详细信息的表格。相关表格如下:

商店项目

+---------+-----------+--------+
| item_id | item_name | status |
+---------+-----------+--------+
| 1 | Diesel | 1 |
| 2 | Petrol | 1 |
+---------+-----------+--------+

tbl_vehicle

+------------+---------------+
| vehicle_id | registered_no |
+------------+---------------+
| 1 | LE-7476 |
| 2 | 270-0523 |
+------------+---------------+

tbl_direct_fuel

+----------------+----------+---------+------------+------------+
| direct_fuel_id | vehicle | orderNo | issue_date | milo_meter |
+----------------+----------+---------+------------+------------+
| 1 | LE-7476 | 173072 | 2019-11-12 | 30,000 |
| 2 | LE-7476 | 173069 | 2019-11-08 | 29,600 |
| 3 | LE-7476 | 173059 | 2019-11-05 | 29,000 |
| 4 | LE-7476 | 173055 | 2019-10-08 | 25,000 |
| 5 | 270-0523 | 173068 | 2019-11-02 | 10,000 |
| 6 | 270-0523 | 173067 | 2019-10-02 | 8,500 |
+----------------+----------+---------+------------+------------+

tbl_direct_fuel_details

+------------------------+----------------+------+----------+------------+
| direct_fuel_details_id | direct_fuel_id | item | fuel_qty | fuel_price |
+------------------------+----------------+------+----------+------------+
| 100 | 1 | 1 | 20 | 105 |
| 101 | 2 | 1 | 15 | 105 |
| 102 | 3 | 1 | 12 | 105 |
| 103 | 4 | 2 | 50 | 165 |
| 104 | 5 | 1 | 25 | 100 |
| 105 | 6 | 2 | 18 | 165 |
+------------------------+----------------+------+----------+------------+

期望的输出

我想获得 no_of_kms,与使用 issue_date 和 milo_meter(当前 milo_meter - 以前的 milo_meter)发布的燃料数量。输出如下:

+----------+-----------+
| vehicle | no_of_kms |
+----------+-----------+
| LE-7476 | 400 |
| LE-7476 | 600 |
| LE-7476 | 4,000 |
| 270-0523 | 1500 |
+----------+-----------+

我使用了以下查询:

select v1.registered_no as vehicle, si.item_name as fuel, df.milo_meter - df.milo_meter  as no_of_kms
from (select dfd.item, sum(dfd.fuel_qty) AS qty
from tbl_direct_fuel df
join tbl_direct_fuel_details dfd on df.direct_fuel_id = dfd.direct_fuel_id
join tbl_vehicle v1 on df.vehicle = v1.vehicle_id
where df.status = 1
group by registered_no) dfd
join store_item si on dfd.item = si.item_id
join (select item, sum(dfd.fuel_qty) AS fuel_qty
from tbl_direct_fuel_details
group by item) dfd on si.item_id=dfd.item

但是上面的查询没有正常工作。可能出了什么问题?谁能帮帮我?

最佳答案

如果您运行的是 MySQL 8.0,您可以使用窗口函数 lag() 简单地执行此操作:

select *
from (
select
issue_date,
vehicle,
milo_meter
- lag(milo_meter) over(partition by vehicle order by issue_date) no_of_kms
from tbl_direct_fuel
) t
where no_of_kms is not null
order by vehicle desc, issue_date desc

我将 issue_date 添加到输出列中,因为这似乎是理解结果的明智信息。


在早期版本中,我认为内联查询可能会成功:

select *
from (
select
issue_date,
vehicle,
milo_meter - (
select max(milo_meter)
from tbl_direct_fuel d1
where d1.vehicle = d.vehicle and d1.issue_date < d.issue_date
) no_of_kms
from tbl_direct_fuel d
) t
where no_of_kms is not null
order by vehicle desc, issue_date desc

这假设给定车辆的 milo_meter 只能增加,这似乎是一个合理的假设。

Demo on DB Fiddle

两个查询都返回:

issue_date | vehicle  | no_of_kms:--------- | :------- | --------:2019-11-12 | LE-7476  |       4002019-11-08 | LE-7476  |       6002019-11-05 | LE-7476  |      40002019-11-02 | 270-0523 |      1500

关于MySQL 表值与当前日期和上一个日期的差异,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58840459/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com