使用"GROUP BY"将多个表连接起来和MySQL中的最新日期 [英] join multiple tables with "GROUP BY" and latest date in MySQL
问题描述
我有2个mysql表
表1是"qualitaet-inventar",具有3000行,其中"sachnummer"为AUTO_INCREMENT
table 1 is "qualitaet-inventar" and has 3000 rows with "sachnummer" as AUTO_INCREMENT
表2是"qualitaet",并且有几行..这里的"id"设置为AUTO_INCREMENT 重要的是"sachnummer"可以存在很多次. (请参见图片中的红色标记)
table 2 is "qualitaet" and has a couple of rows.. here is "id" set to AUTO_INCREMENT the important thing is that "sachnummer" can be exist many times. (see the red marks in picture)
我想做的是从"qualitaet"到"qualitaet-inventar"的左连接
what i want to do is a left join from "qualitaet" to "qualitaet-inventar"
我想通过在"qualitaet"上显示创建"的最新日期来对所有"sachnummer"进行分组
i want to group all "sachnummer" by showing the latest date of "created" on "qualitaet"
并且当"qualitaet"中没有"sachnummer"时,请空连接"qualitaet-inventar"
and when a "sachnummer" isn't available in "qualitaet" do an empty join to "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
问题是要花很长时间才能得到这个结果
the problem is that it needs a long time to get this result
使用此代码,它的运行速度更快
with this code it works faster
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
但是我没有得到此代码的孔库存和空日期
but i don't get the hole inventory and an empty date with this code
是否有可能以更快的方式获得较高的表行数? :D
is it possible to get this table in a faster way for high table-rows? :D
这是我的索引
推荐答案
以下不应的运行速度比现有查询快-但从您的描述来看,可能做:
The following shouldn't work any faster than your existing query - but from your description, may do:
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
这篇关于使用"GROUP BY"将多个表连接起来和MySQL中的最新日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!