在 MySQL 中如何为每个组选择前 2 行？
要从每个组中选择前2行，请在子查询中使用where条件。让我们创建一个表。创建表的查询如下：
mysql> create table selectTop2FromEachGroup -> ( -> Name varchar(20), -> TotalScores int -> );
现在使用插入命令在表中插入一些记录。查询如下：
mysql> insert into selectTop2FromEachGroup values('John',32);
mysql> insert into selectTop2FromEachGroup values('John',33);
mysql> insert into selectTop2FromEachGroup values('John',34);
mysql> insert into selectTop2FromEachGroup values('Carol',35);
mysql> insert into selectTop2FromEachGroup values('Carol',36);
mysql> insert into selectTop2FromEachGroup values('Carol',37);使用select语句显示表中的所有记录。查询如下：
mysql> select *from selectTop2FromEachGroup;
以下是输出：
+-------+-------------+ | Name | TotalScores | +-------+-------------+ | John | 32 | | John | 33 | | John | 34 | | Carol | 35 | | Carol | 36 | | Carol | 37 | +-------+-------------+ 6 rows in set (0.00 sec)
这是使用where条件和子查询从每个组中选择前2行的查询：
mysql> select *from selectTop2FromEachGroup tbl -> where -> ( -> SELECT COUNT(*) -> FROM selectTop2FromEachGroup tbl1 -> WHERE tbl1.Name =tbl.NameAND -> tbl1.TotalScores >= tbl.TotalScores -> ) <= 2 ;
以下是输出：
+-------+-------------+ | Name | TotalScores | +-------+-------------+ | John | 33 | | John | 34 | | Carol | 36 | | Carol | 37 | +-------+-------------+ 4 rows in set (0.06 sec)