一、对分组的记录取前N条记录:例如:取每组的前3条最大的记录
1.用子查询:
SELECT * FROM tableName a WHERE
(SELECT COUNT(*) FROM tableName b WHERE b.id=a.id AND b.cnt>a.cnt) < 3
ORDER BY a.id,a.account DESC
where中的select是保证:遍历所有记录,取每条记录与当前记录做比较,只有当tableName表中同一id不超过3个比当前高时,这条数据才算是排行的前三名。
2.用exists半连接:
SELECT * FROM tableName a WHERE EXISTS
(SELECT COUNT(*) FROM tableName b WHERE b.id=a.id AND a.account<b.cnt HAVING COUNT(*)<3)
ORDER BY a.id,a.cntDESC
二、同理可以取组内最小的N条记录:例如:取每组的前3条最小的记录
1、子查询
SELECT * FROM tableName a WHERE 3>
(SELECT COUNT(*) FROM tableName b WHERE b.id=a.id AND b.cnt<a.cnt)
ORDER BY a.id,a.cnt DESC
2、用exists:
SELECT * FROM tableName a WHERE EXISTS
(SELECT COUNT(*) FROM tableName b WHERE b.id=a.id AND a.cnt>b.cnt HAVING COUNT(*)<3)
ORDER BY a.id,a.cnt DESC