MySQL Max函数混合行 [英] MySQL Max function mixing rows

问题描述

似乎有一个视图：

  id rev state 
 1 100 pass 
 1 99 fail 
 1 98 fail 
  

结果应为：

  id rev state 
 1 100 ** pass ** 
  

  id rev state 
 1 100 ** fail ** 
 
 SELECT r.id ，r.state，MAX（r.revision）
 FROM VIEW_data r 
 WHERE r.id = 1 
  

MAX（）

code>。 MySQL允许你省略它（其他RDBMS将报告错误），但有不确定的结果，你看到。这可以通过加入一个返回分组 rev 每 id 的子查询来处理。

  SELECT 
 r.id，
 r.state，
 maxrev.rev 
 FROM 
 VIEW_data r 
 / * INNER JOIN针对每个ID返回MAX（rev）的子查询* / 
 JOIN（
 SELECT id，MAX（rev）AS rev 
 FROM VIEW_data GROUP BY id 
 / * JOIN同时在id和rev上为状态拉取正确的值* / 
）maxrev ON r.id = maxrev.id AND r.rev = maxrev.rev 
 WHERE r.id = 1 
  

http://sqlfiddle.com/#!2/4f651/8

上述操作将返回 id 的最大值 rev 的值。如果你是某些，只需要 WHERE 子句而不是 MAX（）每组，看另一个答案，使用 ORDER BY & LIMIT 。

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

http://sqlfiddle.com/#!2/4f651/8

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.

这篇关于MySQL Max函数混合行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！