如何在mysql中为每个用户获取最多的行

内容导读

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

内容图文

user_id  category     suburb      dated        walk_time
1        experience    US      2016-04-09        5
1        discovery     US      2016-04-09        5
1        experience    UK      2016-04-09        5
1        experience    AUS     2016-04-23        10
2        actions       IND     2016-04-15        2
2        actions       IND     2016-04-15        1
2        discovery     US      2016-04-21        2
3        discovery     FR      2016-04-12        3
3        Emotions      IND     2016-04-23        3
3        discovery     UK      2016-04-12        4
3        experience    IND     2016-04-12        3

我正在尝试让每个用户最常用的类别,郊区,约会,walk_time

所以结果表是

user_id  category     suburb      dated       walk_time
1        experience    US      2016-04-09        5
2        actions       IND     2016-04-15        2
3        discovery     IND     2016-04-12        3

我在这里尝试的查询是

select user_id,
       substring_index(group_concat(suburb order by cnt desc), ',', 1) as suburb_visited,
       substring_index(group_concat(category order by cct desc), ',', 1) as category_used,
       substring_index(group_concat(walk_time order by wct desc), ',', 1) as walked,
       substring_index(group_concat(dated order by nct desc), ',', 1) as dated_at
from (select user_id, suburb, count(*) as cnt,category, count(*) cct, walk_time, count(*) wct, dated,count(*) nct
      from temp_user_notes
      group by user_id, suburb,category,walk_time,dated
     ) upv
group by user_id;

解决方法:

SELECT user_id,
      (SELECT category FROM temp_user_notes t1
       WHERE t1.user_id = T.user_id
       GROUP BY category ORDER BY count(*) DESC LIMIT 1) as category,
      (SELECT suburb FROM temp_user_notes t2
       WHERE t2.user_id = T.user_id
       GROUP BY suburb ORDER BY count(*) DESC LIMIT 1) as suburb,
      (SELECT dated FROM temp_user_notes t3
       WHERE t3.user_id = T.user_id
       GROUP BY dated ORDER BY count(*) DESC LIMIT 1) as dated,
      (SELECT walk_time FROM temp_user_notes t4
       WHERE t4.user_id = T.user_id
       GROUP BY walk_time ORDER BY count(*) DESC LIMIT 1) as walk_time
FROM (SELECT DISTINCT user_id FROM temp_user_notes) T

http://sqlfiddle.com/#!9/8aac6a/19

内容总结

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

内容备注

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

内容手机端