問題描述
以下是最簡單的示例，但任何解決方案都應該能夠擴展到需要多少 n 個頂級結果:
給定如下表，其中包含人、組和年齡列，您將如何獲得每個組中最年長的 2 個人?(組內的關系不應產生更多結果，而是給出按字母順序排列的前 2 個)
<前>+--------+-------+-----+|人 |集團 |年齡 |+--------+-------+-----+|鮑勃 |1 |32 ||吉爾 |1 |34 ||肖恩 |1 |42 ||杰克 |2 |29 ||保羅 |2 |36 ||勞拉 |2 |39 |+--------+-------+-----+期望的結果集:
<前>+--------+-------+-----+|肖恩 |1 |42 ||吉爾 |1 |34 ||勞拉 |2 |39 ||保羅 |2 |36 |+--------+-------+-----+<小時>
注意:這個問題建立在上一個問題的基礎上 - 獲取每組分組的最大值的記錄SQL 結果 - 用于從每個組中獲得一個頂行，并且從 @Bohemian 那里收到了一個很好的 MySQL 特定答案:
選擇 *from (select * from mytable order by `Group`, Age desc, Person) x按組"分組希望能夠以此為基礎，盡管我不知道如何.
這是一種方法，使用 UNION ALL(參見 SQL Fiddle with Demo).這適用于兩個組，如果您有兩個以上的組，則需要指定 group 編號并為每個 group 添加查詢:
<代碼>(選擇 *來自 mytable其中`組`= 1按年齡排序限制 2)聯合所有(選擇 *來自 mytable其中`組`= 2按年齡排序限制 2)
有多種方法可以做到這一點，請參閱本文以確定適合您情況的最佳路線:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
這也可能對您有用，它為每條記錄生成一個行號.使用上面鏈接中的示例，這將僅返回行數小于或等于 2 的那些記錄:
選擇人物，`group`，年齡從(選擇人、組"、年齡、(@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number從測試 t交叉連接(選擇@num:=0，@group:=null)c按組"、年齡、人排序) 作為 x其中 x.row_number <= 2;參見演示
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
這篇關于獲取每組分組結果的前 n 條記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！