我有一个MySQL数据库，其中包含车辆、已发放燃油和里程数据(与已发放燃油相比(，并希望获得以下所需输出。
所需输出
我想得到包括以上相关数据的结果如下：
+---------------+------------+------------+---------+----------+
| registered_no | issue_date | date | mileage | fuel_qty |
+---------------+------------+------------+---------+----------+
| SP KR-3503 | 2021-02-22 | null | null | 40 |
| SP KR-3503 | 2021-02-26 | null | null | 30 |
| null | 2021-03-03 | null | null | 40 |
| null | 2021-03-15 | null | null | 45 |
| SP KR-3503 | 2021-03-18 | null | null | 40 |
| null | 2021-03-25 | null | null | 45 |
| null | 2021-04-04 | null | null | 35 |
| SP KE-6794 | 2021-04-25 | 2021-04-25 | 150 | 40 |
+---------------+------------+------------+---------+----------+
因为表"；tbl_ trip_details"；对于车辆_no"仅包括一个里程(150km(；SP KE-6794，日期为2021-04-25。我的表格如下：
tbl_vehicle
+------------+---------------+--------+
| vehicle_id | registered_no | status |
+------------+---------------+--------+
| 1 | SP KR-3503 | 1 |
| 2 | SP KE-6794 | 1 |
+------------+---------------+--------+
tbl_trip_details
+---------+------------+------------+---------+--------+
| trip_id | vehicle_id | date | mileage | status |
+---------+------------+------------+---------+--------+
| 1 | 1 | 2021-04-25 | 125 | 1 |
| 2 | 1 | 2021-04-26 | 100 | 1 |
| 3 | 2 | 2021-04-25 | 150 | 1 |
+---------+------------+------------+---------+--------+
tbl_direct_fuel
+----------------+---------+------------+
| direct_fuel_id | vehicle | issue_date |
+----------------+---------+------------+
| 1 | 1 | 2021-02-22 |
| 2 | 1 | 2021-02-26 |
| 3 | 3 | 2021-03-03 |
| 4 | 3 | 2021-03-15 |
| 5 | 1 | 2021-03-18 |
| 6 | 3 | 2021-03-25 |
| 7 | 3 | 2021-04-04 |
| 8 | 2 | 2021-04-25 |
+----------------+---------+------------+
tbl_direct_fuel_details
+-----------------------------+----------------+------+----------+
| tbl_deirect_fuel_details_id | direct_fuel_id | item | fuel_qty |
+-----------------------------+----------------+------+----------+
| 1 | 1 | 1 | 40 |
| 2 | 2 | 1 | 30 |
| 3 | 3 | 1 | 40 |
| 4 | 4 | 1 | 45 |
| 5 | 5 | 1 | 40 |
| 6 | 6 | 1 | 45 |
| 7 | 7 | 1 | 35 |
| 8 | 8 | 1 | 40 |
+-----------------------------+----------------+------+----------+
我使用了以下查询
select tv.registered_no, df.issue_date, td.date, td.mileage, df.fuel_qty
from tbl_trip_details trip
left join tbl_vehicle tv on tv.vehicle_id=trip.vehicle_id
join (select tbl_direct_fuel.direct_fuel_id, tbl_vehicle.registered_no, tbl_direct_fuel.vehicle, tbl_direct_fuel.issue_date, item, tbl_direct_fuel_details.fuel_qty, tbl_direct_fuel_details.fuel_price
from tbl_direct_fuel_details
left join tbl_direct_fuel on tbl_direct_fuel_details.direct_fuel_id = tbl_direct_fuel.direct_fuel_id
join tbl_vehicle on tbl_vehicle.vehicle_id = tbl_direct_fuel.vehicle
where tbl_direct_fuel.status=1
order by tbl_vehicle.registered_no
) AS df on df.vehicle = tv.vehicle_id
join (select tbl_trip_details.trip_id, tbl_vehicle.registered_no, tbl_trip_details.vehicle_id, tbl_trip_details.date, tbl_trip_details.mileage
from tbl_trip_details
left join tbl_vehicle on tbl_vehicle.vehicle_id = tbl_trip_details.vehicle_id
where tbl_trip_details.status=1
order by tbl_vehicle.registered_no) AS td on td.vehicle_id = tv.vehicle_id
自&对其他记录重复里程数，但没有得到预期结果。因此，查询输出如下：
+---------------+------------+------------+---------+----------+
| registered_no | issue_date | date | mileage | fuel_qty |
+---------------+------------+------------+---------+----------+
| SP KR-3503 | 2021-02-22 | 2021-04-25 | 150 | 40 |
| SP KR-3503 | 2021-02-26 | 2021-04-25 | 150 | 30 |
| null | 2021-03-03 | 2021-04-25 | 150 | 40 |
| null | 2021-03-15 | 2021-04-25 | 150 | 45 |
| SP KR-3503 | 2021-03-18 | 2021-04-25 | 150 | 40 |
| null | 2021-03-25 | 2021-04-25 | 150 | 45 |
| null | 2021-04-04 | 2021-04-25 | 150 | 35 |
| SP KE-6794 | 2021-04-25 | 2021-04-25 | 150 | 40 |
+---------------+------------+------------+---------+----------+
我的问题可能出了什么问题？有人能帮忙吗？
尝试此查询：
SELECT tv.registered_no, tdf.issue_date, ttd.date, ttd.mileage, tdfd.fuel_qty
FROM tbl_direct_fuel tdf
LEFT JOIN tbl_vehicle tv
ON tdf.vehicle=tv.vehicle_id
LEFT JOIN tbl_trip_details ttd
ON tdf.vehicle=ttd.vehicle_id
AND tdf.issue_date=ttd.date
LEFT JOIN tbl_direct_fuel_details tdfd
ON tdf.direct_fuel_id=tdfd.direct_fuel_id;
演示小提琴
我尝试使用一堆LEFT JOIN直接获得结果，而不是使用两个单独的子查询。从更新后的预期结果中可以看出，tbl_direct_fuel足以作为所有其他结果的参考表。我只是想弄清楚正确的ON条件。
此外，我还提到了样本数据中缺少的tbl_direct_fuel.status和tbl_direct_fuel_details.fuel_price。我并不特别关心tbl_direct_fuel_details.fuel_price，因为它没有包括在最终的SELECT结果中。然而，tbl_direct_fuel.status在WHERE中使用可能会产生一些影响。但是在构造了可能的查询之后，我假设在这个例子中，根本不需要包含tbl_direct_fuel.status。不管怎样，这是你自己想的。