mysql – 如何获得每个组的最大行？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何获得每个组的最大行？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2797字，纯文字阅读大概需要4分钟。

内容图文

我有一个数据库,人们为不同地方的人提交投票.在某个时间,我想找出每个地方谁拥有最多选票. (一个人可以在两个不同的地方投票)

这是我到目前为止的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

这给了我这个结果：

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

我的查询中缺少什么,所以我可以得到这个？

>我想要每个地方一个结果.所以我应该只看到不同的placeIds,其中userVotedId获得了最多的选票.
>如果出现平局,随机获胜者将会做到！

解决方法:

好像你还需要一个聚合.在你的cnt值和GROUP BY placeId,userVotedId上使用MAX()聚合：

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 with Demo

结果是：

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

编辑#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

见SQL Fiddle with Demo

内容总结

以上是互联网集市为您收集整理的mysql – 如何获得每个组的最大行？全部内容，希望文章能够帮你解决mysql – 如何获得每个组的最大行？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端