如何获取每个分组的最大行？

Question

如何获取每个分组的最大行？

5

我可以帮忙翻译。以下是翻译的结果：

我有一个数据库，人们可以在不同的地方提交给某人的投票。在特定时间，我想找出每个地方得票最多的人。（一个人可以在两个不同的地方被投票）

这是我目前拥有的SQL：

SELECT placeId, userVotedId, cnt 
FROM 
    (SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
     FROM users as u, users_votes as uvo, places as p 
     WHERE u.userId = uvo.userVotedId 
       AND p.placeId = uvo.placeId 
     GROUP BY userVotedId, placeId) 
AS RESULT

这给了我这个结果：

enter image description here

现在，这些是我真正想要的行：

enter image description here

我的查询缺少什么，以便我可以得到这个结果？

我想要每个地点只有一个结果。因此，我应该只看到唯一的placeIds，其中userVotedId获得了最多的投票。
在平局的情况下，随机选择一个获胜者即可！

- Michael Eilers Smith

你所得到的和你想要的是相同的。因此，你的查询有效！ - cha

@cha 我也是这么认为的，但关键是要消除两个placeid = 51。可能有更易懂的方法来指出这一点，而不是四个颜色不同的红框框。;-) - KekuSemau

如果您正在尝试基于结果的placeID进行分组，并根据cnt获取顶行，那么这是一个比一开始看起来更难的问题。在SQL Server中，我认为您可以使用APPLY来完成此操作，但MySQL不支持该功能。 - Matthew

1

你测试过我的答案了吗？这是fiddle链接（http://sqlfiddle.com/#!2/23bd7/6）。也许不是最优雅的，但它可以工作（如果MySQL支持CTE会更好）。祝你好运！ - sgeddes

3个回答

1

SELECT placeId, userVotedId, MAX(cnt)
    FROM (SELECT uvo.userVotedId, p.placeId, count(*) AS cnt 
          FROM users as u, users_votes as uvo, places as p 
          WHERE u.userId = uvo.userVotedId AND p.placeId = uvo.placeId 
          GROUP BY userVotedId, placeId) AS RESULT
    GROUP BY  placeId

类似问题 - SQL查询max()，count()
演示

- Muhammad Hani

不，我之前试过（在最后按placeId分组），但问题是它没有给出正确的答案。看一下结果：placeId = 51的userVotedId应该是67，但实际上是63。 - Michael Eilers Smith

1

为简单起见，我将您的查询称为“Test”:

SELECT * 
FROM Test T JOIN (
SELECT t.placeId, Max(t.cnt) maxcnt
FROM Test t
GROUP BY t.placeId) T2 ON T.placeId = T2.placeId and T.cnt = T2.maxcnt

这里是Fiddle。

顺便说一下 -- Test = :。

SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
  FROM users as u
  INNER JOIN users_votes as uvo
    ON u.userId = uvo.userVotedId 
  INNER JOIN places as p 
    ON p.placeId = uvo.placeId 
  GROUP BY userVotedId, placeId

我是一个有用的助手，可以翻译文本。

--编辑-- 根据要求，这是最终代码:

SELECT * 
FROM (SELECT uvo.userVotedId, p.placeId, count(*) AS cnt 
          FROM users as u, users_votes as uvo, places as p 
          WHERE u.userId = uvo.userVotedId AND p.placeId = uvo.placeId 
          GROUP BY userVotedId, placeId) T JOIN (
SELECT t.placeId, Max(t.cnt) maxcnt
FROM (SELECT uvo.userVotedId, p.placeId, count(*) AS cnt 
          FROM users as u, users_votes as uvo, places as p 
          WHERE u.userId = uvo.userVotedId AND p.placeId = uvo.placeId 
          GROUP BY userVotedId, placeId) t
GROUP BY t.placeId) T2 ON T.placeId = T2.placeId and T.cnt = T2.maxcnt

- sgeddes

@omegatai -- 我在我的SQL中使用了这个演示，并且它有效 -- 这是fiddle链接：http://sqlfiddle.com/#!2/23bd7/6 - sgeddes

我刚意识到你的解决方案并不完全可行。请参见此处：http://sqlfiddle.com/#!2/6a8f4/1 - Michael Eilers Smith

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

似乎您需要一个额外的聚合。在您的cnt值上使用MAX()聚合，并使用placeId、userVotedId进行分组:

SELECT placeId, userVotedId, max(cnt)
FROM 
(
  SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
  FROM users as u
  INNER JOIN users_votes as uvo
    ON u.userId = uvo.userVotedId 
  INNER JOIN places as p 
    ON p.placeId = uvo.placeId 
  GROUP BY userVotedId, placeId
) AS RESULT
GROUP BY placeId, userVotedId

<注意>：我更改了您的查询，使用了JOIN语法，而不是表之间的逗号。 <根据您的评论进行编辑，以下内容应该可以工作:>

select total.uservotedid,
  total.placeid,
  total.cnt
from
(
  SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
  FROM users as u
  INNER JOIN users_votes as uvo
    ON u.userId = uvo.userVotedId 
  INNER JOIN places as p 
    ON p.placeId = uvo.placeId 
  GROUP BY userVotedId, placeId
) total
inner join
(
  select max(cnt) Mx, placeid
  from
  (
    SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
    FROM users as u
    INNER JOIN users_votes as uvo
      ON u.userId = uvo.userVotedId 
    INNER JOIN places as p 
      ON p.placeId = uvo.placeId 
    GROUP BY userVotedId, placeId
  ) mx
  group by placeid
) src
  on total.placeid = src.placeid
  and total.cnt = src.mx

请看带演示的SQL Fiddle 结果如下：

| USERVOTEDID | PLACEID | CNT |
-------------------------------
|          65 |      11 |   1 |
|          67 |      13 |   1 |
|          67 |      25 |   1 |
|          67 |      51 |   2 |

如果您想在平局时返回一个随机数，则可以使用用户变量：

select uservotedid,
  placeid, 
  cnt
from
(
  select total.uservotedid,
    total.placeid,
    total.cnt,
    @rownum := case when @prev = total.placeid then @rownum+1 else 1 end rownum,
    @prev := total.placeid pplaceid
  from
  (
    SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
    FROM users as u
    INNER JOIN users_votes as uvo
      ON u.userId = uvo.userVotedId 
    INNER JOIN places as p 
      ON p.placeId = uvo.placeId 
    GROUP BY userVotedId, placeId
  ) total
  inner join
  (
    select max(cnt) Mx, placeid
    from
    (
      SELECT uvo.userVotedId, p.placeId, count(*) as cnt 
      FROM users as u
      INNER JOIN users_votes as uvo
        ON u.userId = uvo.userVotedId 
      INNER JOIN places as p 
        ON p.placeId = uvo.placeId 
      GROUP BY userVotedId, placeId
    ) mx
    group by placeid
  ) src
    on total.placeid = src.placeid
    and total.cnt = src.mx
  order by total.placeid, total.uservotedid
) src
where rownum = 1
order by placeid, uservotedid

See SQL Fiddle with Demo