如何获取MySQL中的值计数 [英] how to get count of values in mysql

问题描述

我有一个这样的表user_places:

I have a table user_places like this:

user_id  recorded_date  place_visited
2        2016-04-05     NY
2        2016-04-07     UK
2        2016-04-08     UK
2        2016-04-08     UK
3        2016-04-08     AUS
3        2016-04-09     AUS
2        2016-04-15     NY

我正在尝试获取用户的最新记录日期和他最常去过的地方的名称.

I am trying to get the latest recorded_date of user and name of the place that he has visited the most.

推荐答案

如果数据不是太大，则可以在MySQL中使用此技巧:

If your data is not too large, then you can use this trick in MySQL:

select user_id, max(dte),
       substring_index(group_concat(place_visited order by cnt desc), ',', 1)
from (select user_id, place_visited, count(*) as cnt, max(recorded_date) as dte
      from user_places
      group by user_id, place_visited
     ) upv
group by user_id;

这篇关于如何获取MySQL中的值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！