假如有表 person,有3个字段 id, age, name,类型为 int, int, varchar(10)
测试数据:
1 22 zhangsan
2 22 asds
3 22 sdgg
4 24 fgh
5 24 dgg
6 28 sdg
7 28 dfg
8 28 fdh
9 19 dfg
10 21 fgj查询结果为:
22 zhangsan
22 asds
24 fgh
24 dgg
28 sdg
28 dfg
也就是按年龄分组后 每个只取2条记录。
测试数据:
1 22 zhangsan
2 22 asds
3 22 sdgg
4 24 fgh
5 24 dgg
6 28 sdg
7 28 dfg
8 28 fdh
9 19 dfg
10 21 fgj查询结果为:
22 zhangsan
22 asds
24 fgh
24 dgg
28 sdg
28 dfg
也就是按年龄分组后 每个只取2条记录。
+------+------+----------+
| id | age | name |
+------+------+----------+
| 1 | 22 | zhangsan |
| 2 | 22 | asds |
| 3 | 22 | adgg |
| 4 | 24 | fgh |
| 5 | 24 | dgg |
| 6 | 28 | sdg |
| 7 | 28 | dfg |
| 8 | 28 | fdh |
| 9 | 19 | dfg |
| 10 | 21 | fgj |
+------+------+----------+
10 rows in set (0.00 sec)mysql> SELECT person.age,person.name FROM person,(
-> SELECT SUBSTRING_INDEX(name1,',',2) AS name1,age FROM (
-> SELECT age,GROUP_CONCAT(NAME) AS name1 FROM person
-> GROUP BY age) tb1
-> WHERE name1 LIKE '%,%') tb
-> WHERE FIND_IN_SET (person.name,tb.name1) AND person.age=tb.age;
+------+----------+
| age | name |
+------+----------+
| 22 | zhangsan |
| 22 | adgg |
| 24 | fgh |
| 24 | dgg |
| 28 | dfg |
| 28 | fdh |
+------+----------+
6 rows in set (0.02 sec)mysql>
from person A
where (select count(*) from person where A.age=age and a.id>id)<2
mysql> SELECT person.age,person.name FROM person,(
-> SELECT SUBSTRING_INDEX(name1,',',2) AS name1,age FROM (
-> SELECT age,GROUP_CONCAT(NAME) AS name1 FROM person
-> GROUP BY age) tb1
-> WHERE name1 LIKE '%,%') tb
-> WHERE FIND_IN_SET (person.name,tb.name1) AND person.age=tb.age;
ERROR 1305 (42000): FUNCTION ext.FIND_IN_SET does not exist
分组取前N记录