获取最后一次修改日期，当左键连接两个表mysql时

Question

我有主表advertifier，想在advertiser_id上左联接两个表LayoutVariables和LayoutMapping并在最终结果中获得这两个表列中的最后修改日期。

下面的查询只给出一条记录，我想要advertiser表中的所有值，即使这两个表中没有日期值LayoutVariables和LayoutMapping

SELECT  
ADV.av_id,
ADV.av_advertiser_name,
ADV.av_is_active, 
IF(AVLV.alv_advertiser_id IS NOT NULL, TRUE, FALSE) AS isBrandConfigDone,
IF(AVLM.alm_advertiser_id IS NOT NULL, TRUE, FALSE) AS isLayoutMappingDone,
---here i want the max date between two tables
IF(AVLM.alm_last_modified_on IS NOT NULL AND AVLV.alv_last_modified_on IS NOT NULL,IF(MAX(AVLM.alm_last_modified_on)>MAX(AVLV.alv_last_modified_on), "AVLM", "AVLV"),NULL) AS mm
FROM DB.Advertiser AS ADV 
LEFT JOIN DB.LayoutVariables AS AVLV ON ADV.av_id = AVLV.alv_advertiser_id
LEFT JOIN DB.LayoutMapping AS AVLM ON ADV.av_id = AVLM.alm_advertiser_id 
WHERE ADV.av_is_active=1;

表布局可变

alw_layout_variable_key>var val 40amason_usa_vairaible1624589928

alv_id	alv_adviser_id	alf_layout_variable_value
11	8167	var4	1624550014	2025
12	7690	amazon_usa	2021

您可能可以这样做：

SELECT
ADV.av_id,
ADV.av_advertiser_name,
ADV.av_is_active, 
AVLV.alv_advertiser_id ,
AVLM.alm_advertiser_id ,
IF(AVLV.alv_advertiser_id IS NOT NULL, TRUE, FALSE) AS isBrandConfigDone,
IF(AVLM.alm_advertiser_id IS NOT NULL, TRUE, FALSE) AS isLayoutMappingDone,
(SELECT MAX(lmd) FROM 
(SELECT alm_last_modified_on lmd FROM LayoutMapping UNION
SELECT alv_last_modified_on FROM LayoutVariables) v) AS mm
FROM Advertiser AS ADV 
LEFT JOIN LayoutVariables AS AVLV ON ADV.av_id = AVLV.alv_advertiser_id
LEFT JOIN LayoutMapping AS AVLM ON ADV.av_id = AVLM.alm_advertiser_id 
;

与SELECT列表中的相同子查询进行OR运算，使其成为类似CROSS JOIN的：

SELECT
ADV.av_id,
ADV.av_advertiser_name,
ADV.av_is_active, 
AVLV.alv_advertiser_id ,
AVLM.alm_advertiser_id ,
IF(AVLV.alv_advertiser_id IS NOT NULL, TRUE, FALSE) AS isBrandConfigDone,
IF(AVLM.alm_advertiser_id IS NOT NULL, TRUE, FALSE) AS isLayoutMappingDone,
mm
FROM Advertiser AS ADV 
LEFT JOIN LayoutVariables AS AVLV ON ADV.av_id = AVLV.alv_advertiser_id
LEFT JOIN LayoutMapping AS AVLM ON ADV.av_id = AVLM.alm_advertiser_id 
CROSS JOIN (SELECT MAX(lmd) AS mm FROM 
(SELECT alm_last_modified_on lmd FROM LayoutMapping UNION
SELECT alv_last_modified_on FROM LayoutVariables) v) mx

您最初尝试在整个查询上使用MAX()，而上面的这些方法是与查询的其余部分分离的子查询。

演示小提琴