Mysql LEFT加入每个表中的GROUP BY LAST记录

Question

我想链接几个表，并拥有链接表中每个条目的最后记录。

例如

Tabelle Domain
-- ID -- | -- DOMAIN -- | -- DATE --
1         seite.de      08-09-2020

Tabelle Domain Verify
-- ID -- | -- DOMAIN_ID -- | -- IS SSL -- | -- DATE --
1               1             0            06-09-2020
2              1              1            07-09-2020
3              1              1            08-09-2020

Tabelle Domain SSL
-- ID -- | -- DOMAIN_ID -- | -- SSL NAME -- | -- DATE --
1                   1           NOT             06-09-2020
2                  1            ENCRYPT          07-09-2020
3                  1            ENCRYPT           08-09-2020
Tabelle Revoked:
-- ID -- | -- DOMAIN_ID -- | -- REVOKED -- | -- DATE --
1                  1          1           06-09-2020
2                  1          0           07-09-2020
3                  1          0           08-09-2020

现在，他在域SSL表中向我显示SSL名称不是。但想展示ENCRYPT从08.09 开始的最新帖子

SELECT
domain.id,
domain_verify.domain_id,
domain_verify.ssl_check
domain_ssl.domain_id,
domain_ssl.ssl_name,
domain_ssl_revoked.domain_id,
domain_ssl_revoked.revoked
FROM domain
LEFT JOIN domain_verify
ON domain_verify.domain_id = domain.id
LEFT JOIN domain_ssl
ON domain_ssl.domain_id = domain_verify.id
LEFT JOIN domain_ssl_revoked
ON domain_ssl_revoked.domain_id = domain_verify.id
GROUP BY domain_verify.id ORDER BY domain_verify.date DESC 

Answer 1

考虑通过相应的聚合查询进行连接。为了可读性，下面使用了表别名(即，减少所有domain引用(。

SELECT d.id
, v.domain_id AS v_domain_id, v.ssl_check
, s.domain_id AS s_domain_id, s.ssl_name
, r.domain_id AS r_domain_id, r.revoked
FROM domain d
LEFT JOIN domain_verify v ON v.domain_id = d.id
INNER JOIN 
(SELECT domain_id, MAX(date) as max_date
FROM domain_verify v
GROUP BY domain_id) agg_v
ON  agg_v.domain_id = v.domain_id
AND agg_v.max_date = v.date
LEFT JOIN domain_ssl s ON s.domain_id = v.id
INNER JOIN 
(SELECT domain_id, MAX(date) as max_date
FROM domain_ssl
GROUP BY domain_id) agg_s
ON  agg_s.domain_id = s.domain_id
AND agg_s.max_date = s.date
LEFT JOIN domain_ssl_revoked r ON r.domain_id = v.id
INNER JOIN 
(SELECT domain_id, MAX(date) as max_date
FROM domain_ssl_revoked
GROUP BY domain_id) agg_r
ON  agg_r.domain_id = r.domain_id
AND agg_r.max_date = r.date
ORDER BY v.date DESC 

如果有一天您支持窗口函数(包含在MySQL 8.0+中(，可以考虑用检查最长日期来标记行。

SELECT `id`, `date`, v_domain_id, ssl_check, ssl_namme, revoked
FROM
(SELECT d.id
, v.date, v.domain_id AS v_domain_id, v.ssl_check
, s.domain_id AS s_domain_id, s.ssl_name
, r.domain_id AS r_domain_id, r.revoked
, if(v.date = MAX(v.date) OVER(PARTITION BY v.domain_id), 1, 0) AS latest_v_dt
, if(s.date = MAX(s.date) OVER(PARTITION BY s.domain_id), 1, 0) AS latest_s_dt
, if(r.date = MAX(r.date) OVER(PARTITION BY r.domain_id), 1, 0) AS latest_r_dt
FROM domain d  
LEFT JOIN domain_verify v 
ON v.domain_id = d.id AND latest_v_dt = 1   
LEFT JOIN domain_ssl s
ON s.domain_id = v.id AND latest_s_dt = 1   
LEFT JOIN domain_ssl_revoked r
ON r.domain_id = v.id AND latest_r_dt = 1
) sub    
ORDER BY `date` DESC