mysql获取最常见列值的最新行 [英] mysql get recent row of most common column value
问题描述
首先，我需要获取10个最常见的列值，然后从中获取我需要的最新行.
First I need to get to get the 10 most common column values, and then from them values I need the most recent row.
我可以很容易地获得10个最新的列值，如下所示:
I can get the 10 most recent column values easily enough like this:
SELECT animal, count(*) FROM mammals GROUP BY animal ORDER BY count(*) DESC LIMIT 10;
我还可以获取如下所示的值的最新行:
And I can also get the most recent row of a value like this:
SELECT * FROM mammals where animal = 'monkey' ORDER BY check_date DESC LIMIT 1;
如果可能的话，我需要在一个查询中同时做这两项. 最接近我的人可以做我需要的一切，除了它不获取值的最新行，而是获取第一行.
I need to do both of these in one query if possible. The closest I've got does everything I need except it doesn't get the most recent row of the value, it gets the first row.
SELECT animal, check_date, count(*) FROM mammals GROUP BY animal ORDER BY count(*) DESC LIMIT 10;
我想添加更多信息，因为我不确定它是否足够清楚.
I want to add a bit more info because I'm not sure it's clear enough.
如果我有一个这样的表:
If I have a table like this:
+------------------------+---------------------+
| Monkey | 2017-05-08 19:35:30 |
| Monkey | 2017-05-09 08:33:16 |
| Giraffe | 2017-05-09 08:35:24 |
| Giraffe | 2017-05-09 09:09:25 |
| Monkey | 2017-05-09 09:22:43 |
| Giraffe | 2017-05-09 09:24:23 |
| Giraffe | 2017-05-09 09:25:07 |
| Monkey | 2017-05-09 09:26:00 |
| Lion | 2017-05-09 09:26:17 |
| Lion | 2017-05-09 09:28:35 |
| Gazelle | 2017-05-09 09:29:34 |
| Monkey | 2017-05-09 13:29:39 |
| Gazelle | 2017-05-09 13:35:01 |
| Gazelle | 2017-05-09 14:52:57 |
+------------------------+---------------------+
然后运行上面的第三个查询，结果是这样:
And I run the 3rd query above I end up with this:
+------------------+---------------+----------------+
| Animal | check_date | count(*) |
+------------------+---------------+----------------+
| Monkey | 2017-05-08 19:35:30 | 5 |
| Giraffe | 2017-05-09 08:35:24 | 4 |
| Gazzelle | 2017-05-09 09:29:34 | 3 |
| Lion | 2017-05-09 09:26:17 | 2 |
+------------------+---------------+----------------+
该表显然会比这个大得多，但是可以想象那些是最常见的值.如您所见，第三次查询返回的行是最旧的行，但是我需要最新的行.因此，对于Monkey来说就是"2017-05-09 13:29:39".
The table would obviously be much larger than this, but imagine those are the most common values. As you can see the row the 3rd query returns is the oldest row, but I need the most recent row. So for Monkey it would be '2017-05-09 13:29:39'.
我需要表格看起来像这样:
I need the table to look like this:
+------------------+---------------+----------------+
| Animal | check_date | count(*) |
+------------------+---------------+----------------+
| Monkey | 2017-05-09 13:29:39 | 5 |
| Giraffe | 2017-05-09 09:25:07 | 4 |
| Gazzelle | 2017-05-09 14:52:57 | 3 |
| Lion | 2017-05-09 09:28:35 | 2 |
+------------------+---------------+----------------+
谢谢:)
推荐答案
这应该做您想要的.
select animal, max(check_date) as max_date, count(*) as count
from mammals
group by animal
order by count(*) desc
LIMIT 10;
这是我根据您的示例数据从上述查询中得到的输出.
This is my output from the above query based on your sample data.
Monkey | 2017-05-09 13:29:39.000 | 5
Giraffe | 2017-05-09 09:25:07.000 | 4
Gazelle | 2017-05-09 14:52:57.000 | 3
Lion | 2017-05-09 09:28:35.000 | 2
这篇关于mysql获取最常见列值的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
