MySQL 在SQL中，如何为每个组选择前2行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15969614/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In SQL, how to select the top 2 rows for each group
提问by waitingkuo
I have a table as following:
我有一个表如下：
NAME SCORE
-----------------
willy 1
willy 2
willy 3
zoe 4
zoe 5
zoe 6
Here's the sample
这是示例
The aggregation function for group byonly allow me to get the highest score for each name.
I would like to make a query to get the highest 2 score for each name, how should I do?
的聚合函数group by只允许我获得每个name. 我想进行查询以获得每个的最高 2 分name，我该怎么办？
My expected output is
我的预期输出是
NAME SCORE
-----------------
willy 2
willy 3
zoe 5
zoe 6
回答by John Woo
SELECT *
FROM test s
WHERE
(
SELECT COUNT(*)
FROM test f
WHERE f.name = s.name AND
f.score >= s.score
) <= 2
回答by Taryn
In MySQL, you can use user-defined variables to get a row number in each group:
在 MySQL 中，您可以使用用户定义的变量来获取每个组中的行号：
select name, score
from
(
SELECT name,
score,
(@row:=if(@prev=name, @row +1, if(@prev:= name, 1, 1))) rn
FROM test123 t
CROSS JOIN (select @row:=0, @prev:=null) c
order by name, score desc
) src
where rn <= 2
order by name, score;
See Demo
看演示
回答by Avinash Shastri
If you don't mind having additional column then you can use the following code:
如果您不介意有额外的列，那么您可以使用以下代码：
SELECT Name, Score, rank() over(partition by Name, order by Score DESC) as rank
From Table
Having rank < 3;
Rank function provides rank for each partition, in your case it is name
Rank 函数为每个分区提供排名，在您的情况下它是名称
回答by Jhamman Sharma
SELECT * FROM (
SELECT VD.`cat_id` ,
@cat_count := IF( (@cat_id = VD.`cat_id`), @cat_count + 1, 1 ) AS 'DUMMY1',
@cat_id := VD.`cat_id` AS 'DUMMY2',
@cat_count AS 'CAT_COUNT'
FROM videos VD
INNER JOIN categories CT ON CT.`cat_id` = VD.`cat_id`
,(SELECT @cat_count :=1, @cat_id :=-1) AS CID
ORDER BY VD.`cat_id` ASC ) AS `CAT_DETAILS`
WHERE `CAT_COUNT` < 4
------- STEP FOLLOW ----------
1 . select * from ( 'FILTER_DATA_HERE' ) WHERE 'COLUMN_COUNT_CONDITION_HERE'
2. 'FILTER_DATA_HERE'
1. pass 2 variable @cat_count=1 and @cat_id = -1
2. If (@cat_id "match" column_cat_id value)
Then @cat_count = @cat_count + 1
ELSE @cat_count = 1
3. SET @cat_id = column_cat_id
3. 'COLUMN_COUNT_CONDITION_HERE'
1. count_column < count_number
4. ' EXTRA THING '
1. If you want to execute more than one statement inside " if stmt "
2. IF(condition, stmt1 , stmt2 )
1. stmt1 :- CONCAT(exp1, exp2, exp3)
2. stmt2 :- CONCAT(exp1, exp2, exp3)
3. Final "If" Stmt LIKE
1. IF ( condition , CONCAT(exp1, exp2, exp3) , CONCAT(exp1, exp2, exp3) )
回答by NguyenHuuThin
insert into test values('willy',1)
insert into test values('willy',2)
insert into test values('willy',3)
insert into test values('zoe',4)
insert into test values('zoe',5)
insert into test values('zoe',6)
;with temp_cte
as (
select Name, Score,
ROW_NUMBER() OVER (
PARTITION BY Name
ORDER BY Score desc
) row_num
from test
)
select * from temp_cte
where row_num < 3
回答by user2674659
You can do somthething like this:
你可以这样做：
SET @num :=0, @name :='';
SELECT name, score,
@num := IF( @name= name, @num +1, 1 ) AS row_number,
@name := name AS dummy
FROM test
GROUP BY name, score
HAVING row_number <=2
回答by devilcrab
For this you can do this-
为此，您可以这样做-
http://www.sqlfiddle.com/#!2/ee665/4
http://www.sqlfiddle.com/#!2/ee665/4
but in order to get first 2 query, you should use a ID then run limit for ID like 0,2.
但是为了获得前 2 个查询，您应该使用一个 ID，然后运行 ID 的限制，例如 0,2。
回答by Ronald Rivera
Use this query.
使用此查询。
select * from fruits
where type = 'orange'
order by price limit 2
Solution Here:
https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
解决方案在这里：https:
//www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
