mysql - 使用 "GROUP BY"和 MySQL 中的最新日期连接多个表

Question

我有 2 个 mysql 表

表 1 是“qualitaet-inventar”，有 3000 行，“sachnummer”为 AUTO_INCREMENT

表 2 是“qualitaet”并且有几行..这里是“id”设置为 AUTO_INCREMENT重要的是“sachnummer”可以多次存在。 (见图中红色标记)

我想要做的是从“qualitaet”到“qualitaet-inventar”的左连接

我想通过在“qualitaet”上显示“created”的最新日期来对所有“sachnummer”进行分组

当“sachnummer”在“qualitaet”中不可用时，对“qualitaet-inventar”进行空连接

所以我有这个代码

SELECT 
    i.`sachnummer` AS id,
    MAX(q.`created`) AS letztemessung,
    i.`sachnummer-name` AS sachnummer
FROM
        `qualitaet-inventar` i
LEFT JOIN
        `qualitaet` q on i.`sachnummer` = q.`sachnummer`
GROUP BY
        sachnummer
ORDER BY
    sachnummer ASC

问题是需要很长时间才能得到这个结果

使用这段代码它工作得更快

SELECT 
    q.`sachnummer` AS id,
    MAX(q.`created`) AS letztemessung,
    i.`sachnummer-name` AS sachnummer
FROM
    qualitaet q
LEFT JOIN
    `qualitaet-inventar` i on q.`sachnummer` = i.`sachnummer`
GROUP BY
    sachnummer
ORDER BY
    sachnummer ASC

但是我没有得到这个代码的空洞库存和空日期

对于高表行，是否可以更快地获取此表？ :D

编辑:

这是我的索引

Answer 1

以下不应该比您现有的查询更快 - 但根据您的描述，可能可以:

SELECT i.`sachnummer` AS id,
       MAX(q.`created`) AS letztemessung,
       i.`sachnummer-name` AS sachnummer
FROM `qualitaet-inventar` i
JOIN `qualitaet` q on i.`sachnummer` = q.`sachnummer`
GROUP BY sachnummer
UNION ALL
SELECT i.`sachnummer` AS id,
       q.`created` AS letztemessung,
       i.`sachnummer-name` AS sachnummer
FROM `qualitaet-inventar` i
LEFT JOIN `qualitaet` q on i.`sachnummer` = q.`sachnummer`
WHERE q.`sachnummer` IS NULL
ORDER BY sachnummer ASC

另一种可能性:

select id, max(letztemessung) as letztemessung, max(sachnummer) as sachnummer
from
(SELECT `sachnummer` AS id,
        `created` AS letztemessung,
        '' AS sachnummer
 FROM `qualitaet` q
 UNION ALL
 SELECT `sachnummer` AS id,
        cast(null as datetime) AS letztemessung,
        `sachnummer-name` AS sachnummer
 FROM `qualitaet-inventar` i
) sq
group by id 
ORDER BY sachnummer ASC