問(wèn)題描述
以下是最簡(jiǎn)單的示例，但任何解決方案都應(yīng)該能夠擴(kuò)展到需要多少 n 個(gè)頂級(jí)結(jié)果:
給定如下表，其中包含人、組和年齡列，您將如何獲得每個(gè)組中最年長(zhǎng)的 2 個(gè)人?(組內(nèi)的關(guān)系不應(yīng)產(chǎn)生更多結(jié)果，而是給出按字母順序排列的前 2 個(gè))
<前>+--------+-------+-----+|人 |集團(tuán) |年齡 |+--------+-------+-----+|鮑勃 |1 |32 ||吉爾 |1 |34 ||肖恩 |1 |42 ||杰克 |2 |29 ||保羅 |2 |36 ||勞拉 |2 |39 |+--------+-------+-----+期望的結(jié)果集:
<前>+--------+-------+-----+|肖恩 |1 |42 ||吉爾 |1 |34 ||勞拉 |2 |39 ||保羅 |2 |36 |+--------+-------+-----+<小時(shí)>
注意:這個(gè)問(wèn)題建立在上一個(gè)問(wèn)題的基礎(chǔ)上 - 獲取每組分組的最大值的記錄SQL 結(jié)果 - 用于從每個(gè)組中獲得一個(gè)頂行，并且從 @Bohemian 那里收到了一個(gè)很好的 MySQL 特定答案:
選擇 *from (select * from mytable order by `Group`, Age desc, Person) x按組"分組希望能夠以此為基礎(chǔ)，盡管我不知道如何.
這是一種方法，使用 UNION ALL(參見(jiàn) SQL Fiddle with Demo).這適用于兩個(gè)組，如果您有兩個(gè)以上的組，則需要指定 group 編號(hào)并為每個(gè) group 添加查詢:
<代碼>(選擇 *來(lái)自 mytable其中`組`= 1按年齡排序限制 2)聯(lián)合所有(選擇 *來(lái)自 mytable其中`組`= 2按年齡排序限制 2)
有多種方法可以做到這一點(diǎn)，請(qǐng)參閱本文以確定適合您情況的最佳路線:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
這也可能對(duì)您有用，它為每條記錄生成一個(gè)行號(hào).使用上面鏈接中的示例，這將僅返回行數(shù)小于或等于 2 的那些記錄:
選擇人物，`group`，年齡從(選擇人、組"、年齡、(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number從測(cè)試 t交叉連接(選擇@num:=0，@group:=null)c按組"、年齡、人排序) 作為 x其中 x.row_number <= 2;參見(jiàn)演示
The following is the simplest possible example, though any solution should be able to scale to however many n top results are needed:
Given a table like that below, with person, group, and age columns, how would you get the 2 oldest people in each group? (Ties within groups should not yield more results, but give the first 2 in alphabetical order)
+--------+-------+-----+ | Person | Group | Age | +--------+-------+-----+ | Bob | 1 | 32 | | Jill | 1 | 34 | | Shawn | 1 | 42 | | Jake | 2 | 29 | | Paul | 2 | 36 | | Laura | 2 | 39 | +--------+-------+-----+
Desired result set:
+--------+-------+-----+ | Shawn | 1 | 42 | | Jill | 1 | 34 | | Laura | 2 | 39 | | Paul | 2 | 36 | +--------+-------+-----+
NOTE: This question builds on a previous one- Get records with max value for each group of grouped SQL results - for getting a single top row from each group, and which received a great MySQL-specific answer from @Bohemian:
select *
from (select * from mytable order by `Group`, Age desc, Person) x
group by `Group`
Would love to be able to build off this, though I don't see how.
Here is one way to do this, using UNION ALL (See SQL Fiddle with Demo). This works with two groups, if you have more than two groups, then you would need to specify the group number and add queries for each group:
(
select *
from mytable
where `group` = 1
order by age desc
LIMIT 2
)
UNION ALL
(
select *
from mytable
where `group` = 2
order by age desc
LIMIT 2
)
There are a variety of ways to do this, see this article to determine the best route for your situation:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
Edit:
This might work for you too, it generates a row number for each record. Using an example from the link above this will return only those records with a row number of less than or equal to 2:
select person, `group`, age
from
(
select person, `group`, age,
(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number
from test t
CROSS JOIN (select @num:=0, @group:=null) c
order by `Group`, Age desc, person
) as x
where x.row_number <= 2;
See Demo
這篇關(guān)于獲取每組分組結(jié)果的前 n 條記錄的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！