mysql分组后排序获取数据~

(1)第一种,左关联后比较相同组的成绩,分组a表数据,然后筛选b表成绩比a表成绩大的数量,取最小的三行数据
SELECT a.id,a.gid, a.col2,COUNT(b.id)
FROM
t2 a LEFT JOIN t2 b
ON a.gid=b.gid AND a.col2<=b.col2
GROUP BY a.id,a.gid,a.col2
HAVING COUNT(b.id)<=3
ORDER BY a.gid,a.col2 desc;


(2)第二种使用变量
SELECT	b.id,b.gid, b.col2
FROM
(
SELECT a.*, if(@tmpgid = a.gid, @rank := @rank+1, @rank := 0) as rank, @tmpgid := a.gid
FROM
t2 a
ORDER BY gid, col2 DESC
) b
WHERE rank < 3;


(3)第三种,相当于判断自身数据,同一组中,某个成绩比其他成绩小的次数,取最少的三行数据
SELECT
a.id,a.gid, a.col2
FROM
t2 a
WHERE
(SELECT
COUNT(1)
FROM
t2 b
WHERE
a.gid = b.gid
AND
a.col2 <= b.col2
) < 4
ORDER BY gid,col2 DESC

结果: