mysql下分组取关联表指定提示方法，类似于mssql中的cross apply

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

转至:https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results

通过分组的排序及序号获取条数信息，可以使用到索引，没测试性能，不知道和mssql的cross apply性能差异性为多少，只是能实现相应的效果。

#MySQL 5.7.12
#please drop objects you've created at the end of the script
#or check for their existance before creating
#'\\' is a delimiter CREATE TABLE test
(`Person` varchar(5), `Group` int, `Age` int)
; INSERT INTO test
(`Person`, `Group`, `Age`)
VALUES
('Bob', 1, 32),
('Jill', 1, 34),
('Shawn', 1, 42),
('Jake', 2, 29),
('Paul', 2, 36),
('Laura', 2, 39)
; 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; drop table test