mysql - 如何在mysql中为每个用户获取大多数行

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

我试图让每个用户都能找到最常用的分类，郊区，约会，步行时间
所以得到的表是

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