insert into student(name,classid) values
(1,'yuan',1),
(2,'yuanyongzhi',1),
(3,'zhang',2),
(4,'zhangsan',2),
(5,'lisi',3),
(6,'wangwu',4)想要的结果 即如果classid相同,取ID最大的
+----+-------------+---------+
| id | name | classid |
+----+-------------+---------+
| 6 | wangwu | 4 |
| 5 | lisi | 3 |
| 4 | zhangsan | 2 |
| 2 | yuanyongzhi | 1 |
+----+-------------+---------+
(1,'yuan',1),
(2,'yuanyongzhi',1),
(3,'zhang',2),
(4,'zhangsan',2),
(5,'lisi',3),
(6,'wangwu',4)想要的结果 即如果classid相同,取ID最大的
+----+-------------+---------+
| id | name | classid |
+----+-------------+---------+
| 6 | wangwu | 4 |
| 5 | lisi | 3 |
| 4 | zhangsan | 2 |
| 2 | yuanyongzhi | 1 |
+----+-------------+---------+
insert into student(id,name,classid) values
(1,'yuan',1),
(2,'yuanyongzhi',1),
(3,'zhang',2),
(4,'zhangsan',2),
(5,'lisi',3),
(6,'wangwu',4)
http://topic.csdn.net/u/20091231/16/2f268740-391e-40f2-a15e-f243b2c925ab.html
from student s
where not exists(select 1 from student where classid=s.classid and id>s.id)
LEFT JOIN t2v b
ON a.classid=b.classid AND a.id <=b.id
GROUP BY a.id,a.name,a.classid
HAVING COUNT(b.id) <=3
SELECT a.id,a.name,a.classid FROM t2v a
WHERE 3>=(
SELECT COUNT(*) FROM t2v b
WHERE a.classid=b.classid AND a.id <=b.id)
-> a.classid and id>a.id) order by id desc;
+------+-------------+---------+
| id | name | classid |
+------+-------------+---------+
| 6 | wangwu | 4 |
| 5 | lisi | 3 |
| 4 | zhangsan | 2 |
| 2 | yuanyongzhi | 1 |
+------+-------------+---------+