MySQL SELECT n基于GROUP BY记录 [英] MySQL SELECT n records base on GROUP BY
问题描述
可以说我有SQL记录:
国家|编号
美国| 300
美国| 450
美国| 500
美国| 100
UK | 300
UK | 400
UK | 1000
我正在做这样的事情: SELECT * FROM table GROUP BY Country
。
如果我想选择仅在每个国家/地区显示2个最大数字的结果,该怎么办?
$ b 结果将会是:编号
美国| 450
美国| 500
UK | 400
UK | 1000
样本数据
创建表数据(Country varchar(10),Number int);
插入数据选择
'USA',300 union all select
'USA',450 union all select
'USA',500 union all select
'USA ',100工会全部选择
'FR',100工会全部选择
'FR',420工会全部选择
'英国',300工会全部选择
'英国', 400工会全部选择
'UK',1000;
第一个选项是使用像Scrum Meister所显示的变量的伪等级,但是在此呈现为单一语句
SELECT国家,数字
FROM(
SELECT
数字,
@r:= @ c = country时的情况@ r + 1 else 1 rownum,
@c:=国家国家
FROM(选择@r:= 0,@c:='' )x,数据
ORDER BY Country,Number DESC
)y
WHERE rownum< 3;
如果你在前端使用这个,只需要2个计数,那么你可以使用这个返回一个列表中的计数(单列)
SELECT
国家,
left(x ,找到(',',concat(x,','),locate(',',x)+1)-1)Numbers
FROM(
SELECT
a.Country,
Group_Concat(a.Number)x
来自(
选择国家,数据
数据
按国家排序,数字desc)a
组合.Country
)b
结果是
国家;数字
FR;420,100
UK;1000,400
USA;500,450
如果可能发生关系,则第二个表单删除了关系,并显示了每个国家/地区排名前2位的不同数字,作为记录。
SELECT distinct x.Country,x .Number
从数据x
内部连接
(
SELECT
国家,
left(x,locate(',',concat(x,','),locate(',',x)+1)-1)Numbers
FROM(
SELECT
a.Country,
Group_Concat(a.Number)x
从(
)选择不同的国家/地区,编号
数据
按国家排序,数字desc)a
组合a.Country
)b
)y on x.Country = y.Country
and concat(' ,',y.Numbers,',')like concat('%,',x.Number,',%')
order by x.Country,x.Number Desc
$ c
$结果
国家; Number
FR;420
FR;100
UK;1000
UK;400
USA;500
USA;450
Lets say I have SQL records:
Country | Number
USA | 300
USA | 450
USA | 500
USA | 100
UK | 300
UK | 400
UK | 1000
And I am doing something like this: SELECT * FROM table GROUP BY Country
.
What if, I want to choose to display the result with 2 greatest number only in each country? How can I archive this?
The result would be:
Country | Number
USA | 450
USA | 500
UK | 400
UK | 1000
解决方案 Sample data
create table data (Country varchar(10), Number int);
insert into data select
'USA' , 300 union all select
'USA' , 450 union all select
'USA' , 500 union all select
'USA' , 100 union all select
'FR' , 100 union all select
'FR' , 420 union all select
'UK' , 300 union all select
'UK' , 400 union all select
'UK' , 1000;
The first option is a pseudo rank using variables like The Scrum Meister has shown, but presented here as a single statement
SELECT Country, Number
FROM (
SELECT
Number,
@r := case when @c=country then @r+1 else 1 end rownum,
@c := Country Country
FROM (select @r :=0 , @c := '') x, data
ORDER BY Country, Number DESC
) y
WHERE rownum < 3;
If you are using this in a front end, and only need 2 counts, then you can use this form that returns the counts in a list (single column)
SELECT
Country,
left(x,locate(',',concat(x,','),locate(',',x)+1)-1) Numbers
FROM (
SELECT
a.Country,
Group_Concat(a.Number) x
From (
select country, number
from data
order by country, number desc) a
group by a.Country
) b
The result is
"Country";"Numbers"
"FR";"420,100"
"UK";"1000,400"
"USA";"500,450"
If it is possible for ties to occur, then this variation of the 2nd form removes the ties and shows the "top 2 distinct numbers per country", as records.
SELECT distinct x.Country, x.Number
From data x
inner join
(
SELECT
Country,
left(x,locate(',',concat(x,','),locate(',',x)+1)-1) Numbers
FROM (
SELECT
a.Country,
Group_Concat(a.Number) x
From (
select distinct country, number
from data
order by country, number desc) a
group by a.Country
) b
) y on x.Country=y.Country
and concat(',',y.Numbers,',') like concat('%,',x.Number,',%')
order by x.Country, x.Number Desc
Result
"Country";"Number"
"FR";"420"
"FR";"100"
"UK";"1000"
"UK";"400"
"USA";"500"
"USA";"450"
这篇关于MySQL SELECT n基于GROUP BY记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!