嵌套JOIN以创建自定义动态列

Question

我有一个表veicoli(车辆(，如下所示：

-------------------------------
| ID  |   Modello |   Targa   | 
-------------------------------
| 1   |   IVECO   |   XA123WE |     
-------------------------------
| 2   |   IVECO   |   CF556XD |   
-------------------------------
| 3   |   FIAT    |   AS332ZZ | 
-------------------------------
| 4   |   GOLF    |   GF567YU | 
-------------------------------

对于每辆车，我都没有，一个或多个修订_veicolo(修订((DateExpiring较大的车辆是我需要检查修订是否仍然有效的车辆，基于今天的日期(

-------------------------------------------------------------------
| ID  |   veicoli_ID   |   DateExpiring         |     Pass_Success |
-------------------------------------------------------------------
| 1   |   1            | 2019-07-01             |        1
------------------------------------------------------------------
| 2   |   1            | 2020-10-01             |        0
-------------------------------------------------------------------
| 3   |   2            | 2019-11-25             |        1
-------------------------------------------------------------------
| 4   |   2            | 2018-10-20             |        1
-------------------------------------------------------------------
| 5   |   4            | 2017-10-20             |        1
-------------------------------------------------------------------

基于我上面的例子(今天是2019-10-29(：

车辆：ID=1的修订仍然有效(2020-10-01(，但未通过(Pass_success=0(

车辆：ID=2的修订仍然有效(2019-11-25(并通过(Pass_success=1(

车辆：ID=3尚未修订

车辆：ID=4有修订，但没有激活的修订(上一次过期于2017-10-20(，但最后一次通过检查(Pass_success=1(

我需要的是在我的查询结果上动态创建3个新的自定义列：

-------------------------------------------------------------------------------------------
| ID  |   Modello |   Targa   |  RevisionPresent | RevisionStillActive | LastRevisionPassed |
-------------------------------------------------------------------------------------------
| 1   |   IVECO   |   XA123WE |      true        |   true              |   false  
-------------------------------------------------------------------------------------------
| 2   |   IVECO   |   CF556XD |      true        |   true              |   true
-------------------------------------------------------------------------------------------
| 3   |   FIAT    |   AS332ZZ |       false      |   false             |  false
-------------------------------------------------------------------------------------------
| 4   |   GOLF    |   GF567YU |       true       |   false             |  true
-------------------------------------------------------------------------------------------

我试着从我的旧帖子开始：MYSQL INNER JOIN以获得3种类型的结果

但我很困惑使用嵌套的JOIN

我试着启动小提琴，但我被语法错误卡住了：http://sqlfiddle.com/#！9/3c70bf/2

Answer 1

您需要表和条件聚合的LEFT JOIN：

select v.ID, v.Modello, v.Targa,
max(r.DataScadenzaRevisione is not null) RevisionPresent,
coalesce(max(r.DataScadenzaRevisione >= current_date()), 0) RevisionStillActive,
max(case when r.DataScadenzaRevisione = g.maxdate then r.EsitoPositivo else 0 end) LastRevisionPassed 
from veicoli v 
left join revisioni_veicolo r on r.veicoli_ID = v.id
left join (
select veicoli_id, max(DataScadenzaRevisione) maxdate 
from revisioni_veicolo
group by veicoli_id
) g on g.veicoli_ID = v.id           
group by v.ID, v.Modello, v.Targa

请参阅演示
结果：

| ID  | Modello | Targa   | RevisionPresent | RevisionStillActive | LastRevisionPassed |
| --- | ------- | ------- | --------------- | ------------------- | ------------------ |
| 1   | IVECO   | XA123WE | 1               | 1                   | 0                  |
| 2   | IVECO   | CF556XD | 1               | 1                   | 1                  |
| 3   | FIAT    | AS332ZZ | 0               | 0                   | 0                  |
| 4   | GOLF    | GF567YU | 1               | 0                   | 1                  |

Answer 2

...
LEFT JOIN (SELECT a.veicoli_ID, a.EsitoPositivo AS StatoUltimaRevisione,
a.DataScadenzaRevisione FROM revisioni_veicolo) a
...

这有两个问题。

• 别名a是为此子查询定义的，因此您不能在子查询中引用它。但无论如何，您都不需要限定此子查询中的列——您在其他子查询中没有这样做，所以我不确定您为什么在这种情况下这样做
• 您对此联接没有任何联接条件。MySQL在何时需要联接条件方面有点不一致。但在这种情况下，你需要一个

在我用这两个更正测试了查询之后，它就工作了。

Answer 3

基本上，您只需要查看每个vehicule的最后一次修订即可生成结果集。

您可以使用相关的子查询进行过滤：

select
v.ID,
v.Modello,
v.Targa,
(DataScadenzaRevisione >= now()) RevisionPresent,
(DataScadenzaRevisione >= now() and EsitoPositivo = 1) RevisionStillActive,
(EsitoPositivo = 1) LastRevisionPassed 
from 
veicoli v
left join revisioni_veicolo r 
on r.veicoli_ID = v.ID
and r.DataScadenzaRevisione = (
select max(DataScadenzaRevisione) 
from revisioni_veicolo r1 
where r1.veicoli_ID = v.ID
)

你可以在这个数据库中用你的样本数据来检查结果。

或者您可以使用窗口函数(这需要MySQL 8.0(：

select
v.ID,
v.Modello,
v.Targa,
(DataScadenzaRevisione >= now()) RevisionPresent,
(DataScadenzaRevisione >= now() and EsitoPositivo = 1) RevisionStillActive,
(EsitoPositivo = 1) LastRevisionPassed 
from (
select 
v.*, 
r.*, 
row_number() over(partition by ID order by r.DataScadenzaRevisione desc) rn
from veicoli v
left join revisioni_veicolo r on r.veicoli_ID = v.ID
) where coaelesce(rn, 1) = 1