MYSQL内部连接获得3种类型的结果

Question

我有一辆这样的桌子车：

-------------------------------------------------------
| ID  |   Brand   |   License_plate      |  Model      |
-------------------------------------------------------
| 1   |   IVECO   |   XA123WE            |  GRANDX    |
| 2   |   IVECO   |   CF556XD            |  TOURS     |
| 3   |   FIAT    |   AS332ZZ            |  Punto     |
-------------------------------------------------------

对于每一辆车，我都有一个或多个保险(有较大日期的保险是我需要检查的保险是否仍然有效或不基于今天的日期)。

-------------------------------------
| ID  |   vehicle_ID   |   Expire   |
-------------------------------------
| 1   |   1            | 2018-10-19 |
| 2   |   1            | 2019-10-20 |
| 3   |   2            | 2019-11-25 |
| 4   |   2            | 2018-10-20 |
-------------------------------------

在我上面的例子中，假设今天是2019-10-28，ID 1的车辆保险过期，ID2车辆的保险仍然有效(2019-11-25)，ID3车辆还没有保险。

我不知道如何在PHP中构建一个惟一的查询来获取我的搜索结果：

1. 所有有保险的车辆ID过期(只检查每辆车的最高日期，如果是< NOW() )
2. 所有没有保险的车辆ID

从车辆V中选择V.ID加入保险i ON V.ID = I.vehicle_ID，其中.

• 所有车辆ID与保险仍然有效(只检查每辆车辆的最高日期，如果是>= NOW( )

Answer 1

对于这两个条件，可以尝试使用左联接。

select  v.ID, v.Brand, v.Mode
    , ifnull(t1.max_expire_date, 'expired') expired
    ,  ifnull(t2.vehicle_ID , 'not present') not_present
    , case when t1.max_expire_date is not null then 'present' END AS present
from vehicles 
LEFT JOIN  (
  select  vehicle_ID, max(Expire) max_expire_date
  from  insurance 
  group by  vehicle_ID
) t1 on t1.vehicle_ID = v.id 
    AND t.max_expire_date >= date(NOW())
LEFT JOIN  (
  select  vehicle_ID
  from  insurance 
) t2 ON t2.vehicle_ID = v.id 

如果要使用where条件进行筛选，则不能使用列别名，但应重复代码

select  v.ID, v.Brand, v.Mode
    , ifnull(t1.max_expire_date, 'expired') expired
    ,  ifnull(t2.vehicle_ID , 'not present') not_present
    , case when t1.max_expire_date is not null then 'present' END AS present
from vehicles 
LEFT JOIN  (
  select  vehicle_ID, max(Expire) max_expire_date
  from  insurance 
  group by  vehicle_ID
) t1 on t1.vehicle_ID = v.id 
    AND t.max_expire_date >= date(NOW())
LEFT JOIN  (
  select  vehicle_ID
  from  insurance 
) t2 ON t2.vehicle_ID = v.id 
WHERE ifnull(t1.max_expire_date, 'expired')  = 'expired'

Answer 2

使用左外部联接，您可以执行以下3个查询：-

SELECT vehicles.ID 
FROM vehicles 
INNER JOIN insurance i1 
ON vehicles.ID = i1.vehicle_ID 
LEFT OUTER JOIN insurance i2 
ON i1.vehicle_ID = i2.vehicle_ID 
AND i2.expire > i1.expire
WHERE i2.id IS NULL
AND i1.expire < NOW()

在这里，它为任何以后的保险到期日期做一个左外部连接，并检查i2.id是否为空，以确保没有找到较晚的过期日期，然后检查找到的过期日期。

SELECT vehicles.ID 
FROM vehicles 
LEFT OUTER JOIN insurance i1 
ON vehicles.ID = i1.vehicle_ID 
WHERE i1.id IS NULL

这只是检查没有匹配的保险记录

SELECT vehicles.ID 
FROM vehicles 
INNER JOIN insurance i1 
ON vehicles.ID = i1.vehicle_ID 
LEFT OUTER JOIN insurance i2 
ON i1.vehicle_ID = i2.vehicle_ID 
AND i2.expire > i1.expire
WHERE i2.id IS NULL
AND i1.expire >= NOW()

与第一个查询非常类似，它为任何以后的保险到期日期执行左外部连接，并检查i2.id是否为空，以确保没有找到较晚的过期日期，然后检查已找到的过期日期以检查它是否已过期。