前提
笔者在公司的项目开发中,遇到了一个需求:在做一个报表需求时,报表数据每一条都存在多个版本,默认数据列表展示最新版本数据信息。表设计过程中将基础数据设计为一张表,版本数据设计为一张表,基础数据表与版本一对多关系。在开发过程中遇到Order By 与 Group By 一同使用时失效的情况,特意总结,与大家共同进步学习。
全部数据
sql
SELECT c.id AS componentId, c.`name` AS `name`, c.icon_url AS iconUrl, cv.id AS versionId, cv.url AS url, cv.version AS version, cv.notice AS noctice FROM component c LEFT JOIN component_version cv ON c.id = cv.component_id;
结果
第一版SQL
SELECT
c.id AS componentId,
c.`name` AS `name`,
c.icon_url AS iconUrl,
cv.id AS versionId,
cv.url AS url,
cv.version AS version,
cv.notice AS noctice
FROM
component c
LEFT JOIN component_version cv ON c.id = cv.component_id
GROUP BY
cv.component_id
ORDER BY
c.id DESC
运行结果
运行结果并不是我们想要的,正确结果应该是component_id=1的版本信息应该返回version=V2.0的数据。 失败原因,当进行order by 时数据已经进行分组,所以无法返回V2.0数据。
第二版SQL
SELECT
*
FROM
(
SELECT
c.id AS componentId,
c.`name` AS `name`,
c.icon_url AS iconUrl,
cv.id AS versionId,
cv.url AS url,
cv.version AS version,
cv.notice AS noctice
FROM
component c
LEFT JOIN component_version cv ON c.id = cv.component_id
ORDER BY
cv.id DESC
) t
GROUP BY
t.componentId
运行结果
第二版采用子查询方式,先对版本信息进行倒叙,再对结果进行分组,结果返回与第一版相同(仅展示顺序不同),并不是预期结果,order by 失效。
第三版SQL
SELECT
*
FROM
(
SELECT
c.id AS componentId,
c.`name` AS `name`,
c.icon_url AS iconUrl,
cv.id AS versionId,
cv.url AS url,
cv.version AS version,
cv.notice AS noctice
FROM
component c
LEFT JOIN component_version cv ON c.id = cv.component_id
ORDER BY
cv.id DESC
LIMIT 100
) t
GROUP BY
t.componentId
运行结果
运行结果符合我们的预期,已经将compentId=1且version=V2.0的最新数据返回,但是此条SQL存在一个缺点,就是需要对子SQL进行条数限制,存在局限性,不建议使用。
第四版SQL
SELECT
co.*
FROM
(
SELECT
c.id AS componentId,
c.`name` AS `name`,
c.icon_url AS iconUrl,
cv.id AS versionId,
cv.url AS url,
cv.version AS version,
cv.notice AS noctice
FROM
component c
LEFT JOIN component_version cv ON c.id = cv.component_id
) co
INNER JOIN (
SELECT
MAX(id) AS id
FROM
component_version
GROUP BY
component_id
) t ON t.id = co.versionId
运行结果
运行结果符合预期,先查询出版本表中ID最大的数据并根据component_id进行分组,然后在于左连接执行的SQL进行内连接,此SQL无条数限制,推荐使用。
总结
第一版:先进行了group by 在进行order by 数据结果集已固定,order by自然无效。
第二版:mysql5.7升级以后,对子查询的order by 进行优化,不生效。
第三版:子查询order by 的生效原因是,增加limit条数限制,mysql认为不扫描全表,固order by 生效,但是对sql进行了条数限制,影响使用。
第四版:采用MAX函数取出相同分组条件下最大值,然后以此结果作为条件,再次进行条件限制,查询无限制,可取。