嵌套 JOIN 创建自定义动态列 [英] Nested JOIN to create custom dynamic columns

问题描述

我有一张像这样的桌子veicoli(车辆):

I have a table veicoli (vehicles) like this:

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

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

For each vehicle I have none, one or multiple revisioni_veicolo (revisions) (the one with bigger DateExpiring is the one I need to check if revision is still valid or not based on today date)

-------------------------------------------------------------------
| 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):

Based on my example above (today is 2019-10-29):

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

Vehicle: ID = 1 has a revision still active (2020-10-01) but not passed (Pass_success = 0)

车辆:ID = 2 有一个修订版仍然有效 (2019-11-25) 并通过 (Pass_success = 1)

Vehicle: ID = 2 has a revision still active (2019-11-25) and passed (Pass_success = 1)

车辆:ID = 3 尚未修订

Vehicle: ID = 3 has no revision yet

车辆:ID = 4 有修订，但没有有效修订(最后一次过期于 2017-10-20)但最后一辆通过检查(Pass_success = 1)

Vehicle: ID = 4 has revision, but no active revision (last expired on 2017-10-20) but the last one passed the check (Pass_success = 1)

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

What I need is to have 3 new custom columns created dynamically on my query result:

-------------------------------------------------------------------------------------------
| 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 种结果

I tried to start with my old post: MYSQL INNER JOIN to get 3 types of result

但是我很困惑使用嵌套 JOIN

But I'm very confused using nested JOIN

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

I tried starting a fiddle but i'm stuck on syntax error: http://sqlfiddle.com/#!9/3c70bf/2

推荐答案

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

You need a LEFT JOIN of the tables and conditional aggregation:

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

查看演示.
结果:

See the demo.
Results:

| 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                  |

这篇关于嵌套 JOIN 创建自定义动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！