mysql - PHP Query 没有返回正确的值

Question

我有一张这样的桌子veicoli：

-------------------------------------------------------
| ID  |   Modello |   Targa             |  Marca     |
-------------------------------------------------------
| 1   |   IVECO   |   XA123WE            |  GRANDX    |
-------------------------------------------------------
| 2   |   IVECO   |   CF556XD            |  TOURS     |
-------------------------------------------------------
| 3   |   FIAT    |   AS332ZZ            |  Punto     |
-------------------------------------------------------

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

-------------------------------------------------------------------
| ID  |   veicoli_ID   |   DataScadenzaRevisione | EsitoPositivo  |
-------------------------------------------------------------------
| 1   |   1            | 2019-07-01             |        1
------------------------------------------------------------------
| 2   |   1            | 2020-10-01             |        0
-------------------------------------------------------------------
| 3   |   2            | 2019-11-25             |        1
-------------------------------------------------------------------
| 4   |   2            | 2018-10-20             |        1
-------------------------------------------------------------------

感谢这里的另一篇文章，我实现了我的代码以仅搜索 StatoUltimaRevisione = 1（StatoUltimaRevisione 是按 veicoli_ID 分组的 EsitoPositivo 的值，并且仅与组内的 MAX DataScadenzaRevisione 相关）......但我的查询：

SELECT DISTINCT veicoli.ID, veicoli.Modello, veicoli.Marca,
                t1.MaxDataScadenzaRevisione, t3.StatoUltimaRevisione,
                CASE WHEN t1.MaxDataScadenzaRevisione IS NULL THEN 1 ELSE 0 END AS RevisioneScaduta,
                CASE WHEN t2.veicoli_ID IS NULL THEN 0 ELSE 1 END AS RevisionePresente
FROM veicoli LEFT JOIN ( SELECT veicoli_ID, MAX(DataScadenzaRevisione) AS MaxDataScadenzaRevisione FROM revisioni_veicolo GROUP BY veicoli_ID) t1 on t1.veicoli_ID = veicoli.ID AND t1.MaxDataScadenzaRevisione >= date(NOW())
LEFT JOIN ( SELECT veicoli_ID, MAX(DataScadenzaRevisione) AS MaxDataScadenzaRevisione2, EsitoPositivo AS StatoUltimaRevisione FROM revisioni_veicolo GROUP BY veicoli_ID) t3 ON t3.veicoli_ID = veicoli.ID
LEFT JOIN ( SELECT veicoli_ID FROM revisioni_veicolo ) t2 ON t2.veicoli_ID = veicoli.ID
HAVING StatoUltimaRevisione = 1

我总是得到 ID = 1 的结果 veicolo。为什么？

array( ....
  'ID' => string '1'
  'Modello' => string 'IVECO'
  'Marca' => string 'GRANDX'
  'MaxDataScadenzaRevisione' => string '2020-10-01'
  'StatoUltimaRevisione' => string '1'   <---------------WRONG!!!!!!!
  'RevisioneScaduta' => string '0'
  'RevisionePresente' => string '1'

添加小提琴 2：http ://sqlfiddle.com/#!9/e2e43c/ 5 veicolo ID = 26 不正确...显示 ID 114 而不是 143 和 StatoUltimaRevisione 不正确

标签： mysql