mysql max函数多个,MySQL Max函数混合行

最新推荐文章于 2022-10-17 17:56:22 发布

一只鱼的传说

最新推荐文章于 2022-10-17 17:56:22 发布

阅读量457

点赞数

文章标签： mysql max函数多个

博客讨论了在使用SQL查询时遇到的问题，即MAX函数未配合GROUP BY子句导致行内容混合。解决方案是通过JOIN子句与一个返回每组最大修订版的子查询进行连接，以确保正确获取每个ID的最大状态。此问题在某些数据库系统中可能不会报错，但结果不确定。修复后的查询能确保返回每个ID的最大修订版及其对应的状态。

摘要由CSDN通过智能技术生成

Seems like i'm having fundamental problems using MAX - it's mixing the contents of rows, i think.

There is a View:

id rev state

1 100 pass

1 99 fail

1 98 fail

Result should be:

id rev state

1 100 **pass**

but i get this on the query below

id rev state

1 100 **fail**

SELECT r.id, r.state, MAX(r.revision)

FROM VIEW_data r

WHERE r.id=1

解决方案

You need a GROUP BY clause with the aggregate MAX(). MySQL permits you to omit it (where other RDBMS would report errors) but with indeterminate results, which you are seeing. This can be handled by joining against a subquery which returns the grouped rev per id.

SELECT

r.id,

r.state,

maxrev.rev

FROM

VIEW_data r

/* INNER JOIN against subquery which returns MAX(rev) per id only */

JOIN (

SELECT id, MAX(rev) AS rev

FROM VIEW_data GROUP BY id

/* JOIN is on both id and rev to pull the correct value for state */

) maxrev ON r.id = maxrev.id AND r.rev = maxrev.rev

WHERE r.id = 1

The above will return the max rev value for any id. If you are certain you only need the one row as filtered by the WHERE clause rather than the MAX() per group, look at the other answer which makes use of ORDER BY & LIMIT.