代码之家 › 专栏 › 技术社区 › Sasha Grievus

如何在mysql中将group by和max结合起来,得到字段最大值的完整记录?[副本]

mysql

Sasha Grievus · 技术社区 · 6 年前

这个问题已经有了答案:

SQL select only rows with max value on a column [duplicate] 27个答案

我有一个“wordstyped”表,其中包含“idBook”、“guidUser”、“bookmarkLetter”、“letter”和“attemptWrong”。

idBook  guidUser  bookmarkLetter  letter  attemptWrong
------------------------------------------------------
1       1         100             a       2
1       1         100             a       3
1       1         101             b       6
2       2         101             b       2
2       2         101             b       3
2       2         152             d       7
3       3         153             e       2

我要选择所有记录,其中所有字段都包含“attemptwrong”的最大数目,但有不同的三元组“idbook”、“guiduser”和“bookmarkletter”。我想只要正确使用GROUPBY和MAX运算符,就可以得到这个结果。期望的结果是:

idBook  guidUser  bookmarkLetter  letter  attemptWrong
------------------------------------------------------
1       1         100             a       3
1       1         101             b       6
2       2         101             b       3
2       2         152             d       7
3       3         153             e       2

我可以用这个查询获得最大的尝试次数

SELECT *,MAX(attemptWrong) as maxAttemptWrong FROM wordstyped GROUP BY idBook, guidUser, bookmarkLetter

但它又回来了

idBook  guidUser  bookmarkLetter  letter  attemptWrong  maxAttemptWrong
-----------------------------------------------------------------------
1       1         100             a       2             3
1       1         101             b       6             6
2       2         101             b       2             3
2       2         152             d       7             7
3       3         153             e       2             2

就像在这个小提琴里 http://sqlfiddle.com/#!9/135cf9/1

所以它返回正确的maxattemptworg,但不是正确的记录我错过了什么?

2 回复 | 直到 6 年前

Tim Biegeleisen 6 年前

我相信您需要某种子查询来完成此操作,即使您使用的是分析函数。使用MySQL8之前的方法,我们可以加入 wordstyped 表的子查询,该子查询查找每个书签字母的最大错误尝试次数。这将原始表限制为只包含最终输出中所需的匹配行。

SELECT w1.*
FROM wordstyped w1
INNER JOIN
(
    SELECT bookmarkLetter, guidUser, MAX(attemptWrong) AS maxAttemptWrong
    FROM wordstyped
    GROUP BY bookmarkLetter, guidUser
) w2
    ON w1.bookmarkLetter = w2.bookmarkLetter AND
       w1.guidUser = w2.guidUser AND
       w1.attemptWrong = w2.maxAttemptWrong;

Strawberry 6 年前

SELECT x.* 
  FROM wordstyped x
  JOIN 
     ( SELECT idbook
            , guiduser
            , bookmarkletter
            , MAX(attemptwrong) attemptwrong
         FROM wordstyped 
        GROUP
           BY idbook
            , guiduser
            , bookmarkletter
     ) y
    ON y.idbook = x.idbook
   AND y.guiduser = x.guiduser
   AND y.bookmarkletter = x.bookmarkletter
   AND y.attemptwrong = x.attemptwrong

http://sqlfiddle.com/#!9/135cf9/9

在我看来,视图在mysql中几乎是完全无用的——但是如果你必须拥有一个视图,那么你可以用这种方式重新编写上面的内容……

SELECT x.*
  FROM wordstyped x
  LEFT 
  JOIN wordstyped y
    ON y.idbook = x.idbook 
   AND y.guiduser = x.guiduser 
   AND y.bookmarkletter = x.bookmarkletter 
   AND y.attemptwrong > x.attemptwrong 
 WHERE y.idbook IS NULL;

……它的比例很低,但至少可以使用一个视图。