分类排序并且标注在每类的排序号

一、每行加行号
SET @mycnt = 0;
SELECT (@mycnt := @mycnt + 1) AS rowNum,t.col1,t.col2 FROM t1 AS t
GROUP BY t.col1,t.col2
ORDER BY t.col1,t.col2;

二、每个分类中单独排序，并显示序号
SET @mycnt = 0;
SET @lastBX = '';
SELECT (CASE
WHEN @lastBX=tb1.col2 THEN (@mycnt := @mycnt + 1)
ELSE (@mycnt := 1) END) AS rowNum
,[color=red](@lastBX := tb1.col2) AS lastBX[/color]
,tb1.col1
,tb1.col2
FROM (
SELECT t.col1,t.col2 FROM tbName AS t
GROUP BY t.col1,t.col2 ORDER BY t.col1,t.col2
) AS tb1
ORDER BY tb1.col1,tb1.col2;

注意：红色部分不能省略，虽然结果集不使用

[color=blue]举例：选出表tb_name每类col1中根据col2排名前Num的记录[/color]
SELECT tt1.col1,tt1.col2
FROM (
SELECT (CASE
WHEN @lastBX=tb1.col1 THEN (@mycnt := @mycnt + 1)
ELSE (@mycnt := 1) END) AS rowNum
,(@lastBX := tb1.col1 ) AS lastBX
,tb1.col1
,tb1.col2
FROM tb_name AS tb1
ORDER BY tb1.col1,tb1.col2 DESC
) AS tt1
WHERE tt1.rowNum<@Num