mysql - 选择具有多个具有多个条件的多行的其他表的表的项目 - MYSQL

Question

我有这些桌子；

Mysql 表[点击这里]

考勤表

  +-----+------------+--------+---------+--+
    | id  | atdnc_date | emp_id | isleave |  |
    +-----+------------+--------+---------+--+
    | 138 | 2018-11-22 |   1024 |       1 |  |
    | 133 | 2018-11-21 |      6 |       0 |  |
    | 130 | 2018-11-20 |      6 |       0 |  |
    | 129 | 2018-11-20 |     71 |       1 |  |
    | 126 | 2018-11-19 |    817 |       0 |  |
    | 122 | 2018-11-18 |    355 |       0 |  |
    | 123 | 2018-11-18 |      6 |       0 |  |
    | 190 | 2018-11-17 |    355 |       1 |  |
    | 119 | 2018-11-17 |    817 |       0 |  |
    | 117 | 2018-11-17 |      6 |       0 |  |
    | 116 | 2018-11-17 |     71 |       0 |  |
    | 113 | 2018-11-16 |    817 |       0 |  |
    +-----+------------+--------+---------+--+

司机表

+-----+-----------+-------------+--+
| id  | driver_id | driver_lice |  |
+-----+-----------+-------------+--+
| 131 |       817 | B67408      |  |
| 132 |       450 | B548093     |  |
| 133 |         6 | B1394007    |  |
| 134 |       355 | B772739     |  |
| 135 |      1024 | B204085     |  |
| 136 |        71 | B2442775    |  |
| 137 |     88881 | B2056537    |  |
| 138 |       729 | B8265       |  |
| 139 |       600 | B393570     |  |
| 140 |       748 | B2228230    |  |
+-----+-----------+-------------+--+

车辆表

+-----+---------------+-----------+--+
| id  | vehicle_id    | driver_id |  |
+-----+---------------+-----------+--+
| 137 | VH000137      |       817 |  |
| 138 | VH000138      |       817 |  |
| 139 | VH000139      |       600 |  |
| 140 | VH000140      |      1024 |  |
| 141 | VH000141      |       450 |  |
| 142 | VH000142      |       600 |  |
| 143 | VH000143      |       729 |  |
| 144 | VH000144      |       817 |  |
+-----+---------------+-----------+--+

车辆状态表

+---------------+----------+----------------+--+
| vehicle_id    | order_id | current_status |  |
+---------------+----------+----------------+--+
| VH000137      | OR000130 | completed      |  |
| VH000138      | OR000131 | arrived_to_sup |  |
| VH000139      | OR000132 | completed      |  |
| VH000140      | OR000133 | cancelled      |  |
| VH000141      | OR000134 | completed      |  |
| VH000142      | OR000135 | arrived_to_sup |  |
| VH000143      | OR000136 | arrived_to_sup |  |
| VH000144      | OR000137 | completed      |  |
+---------------+----------+----------------+--+

我想获取所有驱动程序的列表，

   attendance.atdnc_date <> '2018-11-17' AND attendance.isleave<>1

    OR that attendance row is null according to driver
AND 
vehicle_status.current_status = 'cancelled' OR vehicle_status.current_status = 'completed'
OR, OR that vehicle_status row is null according to driver

在这里我尝试了查询：

1)

SELECT * FROM
(SELECT driver.* FROM driver LEFT JOIN (SELECT vehicle.* FROM vehicle_status INNER JOIN vehicle ON vehicle_status.vehicle_id=vehicle.vehicle_id WHERE current_status <> 'completed' AND current_status <> 'cancelled') k
 ON driver.driver_id=k.driver_id INNER JOIN(SELECT driver.driver_id FROM driver LEFT JOIN (SELECT emp_id FROM `attendance` WHERE isleave=1 AND atdnc_date='2018-11-17') as x ON x.emp_id=driver.driver_id WHERE x.emp_id IS NULL ) d ON driver.driver_id=d.driver_id ) as x 

2)

SELECT * FROM driver 
LEFT JOIN vehicle ON driver.driver_id=vehicle.driver_id 
LEFT JOIN vehicle_status ON vehicle.vehicle_id=vehicle_status.vehicle_id 
LEFT JOIN attendance ON driver.driver_id=attendance.emp_id WHERE (vehicle_status.current_status ="completed" OR vehicle_status.current_status ="cancelled" OR vehicle_status.current_status IS NULL) 
AND ((attendance.atdnc_date = '2018-11-17' AND attendance.isleave = 0) OR (attendance.atdnc_date IS NULL) ) GROUP BY driver.driver_id 

3)

SELECT * FROM (SELECT driver.* FROM driver LEFT JOIN (SELECT vehicle.* FROM vehicle_status INNER JOIN vehicle ON vehicle_status.vehicle_id=vehicle.vehicle_id WHERE current_status <> 'completed' AND current_status <> 'cancelled') k ON driver.driver_id=k.driver_id WHERE k.driver_id IS NULL) x 
INNER JOIN (SELECT driver.* FROM driver LEFT JOIN attendance ON driver.driver_id=attendance.emp_id WHERE ((attendance.atdnc_date <> '2018-11-17' AND attendance.isleave <> 0) OR (attendance.atdnc_date IS NULL) )) b ON x.driver_id=b.driver_id

我认为这可行..但在使用大量数据时可能会很慢。4)

  select  b.* FROM (SELECT driver.* FROM driver LEFT JOIN (SELECT vehicle.* FROM vehicle_status INNER JOIN vehicle ON vehicle_status.vehicle_id=vehicle.vehicle_id WHERE current_status <> 'completed' AND current_status <> 'cancelled') k 
ON driver.driver_id=k.driver_id WHERE k.driver_id IS NULL) b 
LEFT JOIN (SELECT driver.driver_id FROM attendance INNER JOIN driver ON attendance.emp_id=driver.driver_id WHERE attendance.atdnc_date='2018-11-17' AND attendance.isleave='1') k ON b.driver_id=k.driver_id 
  WHERE k.driver_id IS NULL  AND status ='1' 

这些查询有时会起作用。但是当出勤和vehicle_status 有多行司机时，这些查询会失败。并显示多个驱动程序或显示不符合条件的驱动程序:(

预期结果

+-----+-----------+-------------+--+
| id  | driver_id | driver_lice |  |
+-----+-----------+-------------+--+
| 132 |       450 | B548093     |  |
| 133 |         6 | B1394007    |  |
| 136 |        71 | B2442775    |  |
| 137 |     88881 | B2056537    |  |
| 138 |       729 | B8265       |  |
| 140 |       748 | B2228230    |  |
| 138 |       1024| B204085     |  |
+-----+-----------+-------------+--+

标签： mysql