时间戳

首页 > 解决方案 > 嵌套 JOIN 以创建自定义动态列

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

问题描述

我有一张这样的桌子veicoli（车辆）：

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

对于我没有的每辆车，一个或多个revisioni_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 to get 3 types of result

但是我很困惑使用嵌套的 JOIN

我尝试启动小提琴，但遇到语法错误：http ://sqlfiddle.com/#!9/3c70bf/2

标签： mysql

解决方案

您需要表的 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                  |

推荐阅读