首页 > 代码库 > mysql下分组取关联表指定提示方法,类似于mssql中的cross apply

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

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

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

 1 #MySQL 5.7.12
 2 #please drop objects youve created at the end of the script 
 3 #or check for their existance before creating
 4 #\\ is a delimiter
 5 
 6 
 7 CREATE TABLE test
 8     (`Person` varchar(5), `Group` int, `Age` int)
 9 ;
10     
11 INSERT INTO test
12     (`Person`, `Group`, `Age`)
13 VALUES
14     (Bob, 1, 32),
15     (Jill, 1, 34),
16     (Shawn, 1, 42),
17     (Jake, 2, 29),
18     (Paul, 2, 36),
19     (Laura, 2, 39)
20 ;
21 
22 select person, `group`, age
23 from 
24 (
25    select person, `group`, age,
26       (@num:=if(@group = `group`, @num +1, if(@group := `group`, 1, 1))) row_number 
27   from test t
28   CROSS JOIN (select @num:=0, @group:=null) c
29   order by `Group`, Age desc, person
30 ) as x 
31 where x.row_number <= 2;
32 
33 
34 drop table test

 

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