每组获得N个MYSQL [英] Get N per group MYSQL
问题描述
IT IS NOT THE SAME QUESTION THAN : Using LIMIT within GROUP BY to get N results per group?
但是我承认这是相似的.
but i admit it's similar.
我需要选择每个人的前2行. 这些行按收到的年份排序
I need to select the first 2 rows per person. the rows are ordered by Year recieved
问题:同一个月可能输入了2个数据(日期输入为YYYY-MM)
Problem : there is a possibility than 2 data were entered the same month (Date is entered YYYY-MM)
我所伴随的查询(在提到的问题之后)被卡在了BIG循环中.
The query I came with (following the refered question) is stuck in an BIG loop.
SELECT *
FROM `table_data` as b
WHERE (
SELECT count(*) FROM `table_data` as a
WHERE a.Nom = b.Nom and a.year < b.year
) <= 2;
样本数据:
A | year | Nom
---------------------
b | 2011-01 | Tim
---------------------
d | 2011-01 | Tim
---------------------
s | 2011-01 | Tim
---------------------
a | 2011-03 | Luc
---------------------
g | 2011-01 | Luc
---------------------
s | 2011-01 | Luc
应该导出:
A | year | Nom
---------------------
b | 2011-01 | Tim
---------------------
d | 2011-01 | Tim
---------------------
a | 2011-03 | Luc
---------------------
g | 2011-01 | Luc
推荐答案
(
-- First get a set of results as if you only wanted the latest entry for each
-- name - a simple GROUP BY from a derived table with an ORDER BY
SELECT *
FROM (
SELECT *
FROM `table_data`
ORDER BY `year` DESC
) `a`
GROUP BY `Nom`
)
UNION
(
-- Next union it with the set of result you get if you apply the same criteria
-- and additionally specify that you do not want any of the rows found by the
-- first operation
SELECT *
FROM (
SELECT *
FROM `table_data`
WHERE `id` NOT IN (
SELECT `id`
FROM (
SELECT *
FROM `table_data`
ORDER BY `year` DESC
) `a`
GROUP BY `Nom`
)
ORDER BY `year` DESC
) `b`
GROUP BY `Nom`
)
-- Optionally apply ordering to the final results
ORDER BY `Nom` DESC, `year` DESC
我确信这样做的方法更短,但是现在我无法为自己的生活弄清楚它是什么.尽管可以正常工作-假设您有一个主键(应该有),并且它称为id
.
I feel sure there is a shorter way of doing it but right now I can't for the life of me work out what it is. That does work, though - assuming you have a primary key (which you should) and that it is called id
.
这篇关于每组获得N个MYSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!