时间戳

首页 > 解决方案 > 带有空数据的 MySQl 子查询输出

mysql - 带有空数据的 MySQl 子查询输出

问题描述

我有一个 MySQL 数据库，其中包含车辆、issued_fuel 和里程数据（与发布的燃料相比），并希望获得以下所需的输出。

期望的输出

我想得到包括上述相关数据的结果，如下所示：

+---------------+------------+------------+---------+----------+
| 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”仅包含 2021-04-25 日期的 vehicle_no“SP KE-6794”的一英里（150 公里）。我的表格如下：

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 |
+---------------+------------+------------+---------+----------+

我的查询可能出了什么问题？任何人都可以帮忙吗？

标签： mysqlcodeigniter

解决方案

试试这个查询：

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。无论如何，这是你自己想办法。

推荐阅读