MYSQL INNER JOIN 得到 3 种类型的结果 [英] MYSQL INNER JOIN to get 3 types of result

问题描述

我有一个像这样的桌子车辆:

I have a table VEHICLES like this:

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

对于每辆车我有一个或多个保险(日期较大的那个是我需要根据今天的日期检查保险是否仍然有效的那个)

For each vehicle I have one or multiple INSURANCE (the one with bigger date is the one I need to check if insurance is still active or not based on today date)

-------------------------------------
| 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 车辆的保险已过期，ID 2 车辆的保险仍然有效(2019-11-25)，车辆 ID 3 还没有保险.

In my example above, assuming today is 2019-10-28, insurance for vehicle with ID 1 is expired, insurance for vehicle with ID 2 is still active (2019-11-25), vehicles ID 3 has no insurance yet.

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

I'm not sure how to build a unique query in PHP to get my result to search:

1. 所有带保险的车辆 ID 都已过期(仅检查每辆车的最高日期，如果是

2. 所有没有保险的车辆 ID

1. All vehicles ID with insurance expired (checking only the highest by date for each vehicle, if is < NOW() )

2. All vehicles ID with no insurance present

SELECT V.ID 
  FROM vehicles V 
  JOIN insurance I
    ON V.ID = I.vehicle_ID
 WHERE ....

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

All vehicles ID with insurance still active (checking only the highest by date for each vehicle, if is >= NOW() )

推荐答案

您可以尝试对两个条件使用左连接

You coudl try using left join for the two condition

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 条件过滤，你不能使用列别名，但你应该重复代码

and if you want filter using a where condition you can't use the column alias but you should repeat the code

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'

这篇关于MYSQL INNER JOIN 得到 3 种类型的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！