由于MySQ没有提供像Oracle的dense_rank()或者row_number() over(partition by)等函数，来实现组内排序，想实现这个功能，还是得自己想想办法，最终通过创建行号实现。
1.建立测试表
# 建表
DROP TABLE test;
CREATE TABLE test (
myNAME VARCHAR (10),
name1 VARCHAR (10),
count1 BIGINT
);
2.删除原有数据
# 删除原有数据
DELETE FROM test;
SELECT * FROM test;
3.插入数据
# 插入数据
INSERT INTO test VALUES(‘1‘,‘a‘,2);
INSERT INTO test VALUES(‘1‘,‘b‘,1);;
INSERT INTO test VALUES(‘1‘,‘c‘,4);
INSERT INTO test VALUES(‘1‘,‘d‘,5);
INSERT INTO test VALUES(‘1‘,‘e‘,7);
INSERT INTO test VALUES(‘1‘,‘f‘,8);
INSERT INTO test VALUES(‘2‘,‘g‘,9);
INSERT INTO test VALUES(‘2‘,‘h‘,0);
INSERT INTO test VALUES(‘2‘,‘i‘,21);
INSERT INTO test VALUES(‘2‘,‘j‘,3);
INSERT INTO test VALUES(‘2‘,‘k‘,4);
INSERT INTO test VALUES(‘2‘,‘1‘,56);
INSERT INTO test VALUES(‘3‘,‘m‘,67);
INSERT INTO test VALUES(‘3‘,‘n‘,89);
INSERT INTO test VALUES(‘3‘,‘o‘,12);
INSERT INTO test VALUES(‘3‘,‘p‘,22);
INSERT INTO test VALUES(‘3‘,‘q‘,23);
INSERT INTO test VALUES(‘3‘,‘r‘,42);
INSERT INTO test VALUES(‘3‘,‘s‘,26);
4.查询
# 查询
SELECT * FROM test;
5.构造行号rownum
#---- 构造rownum ----
SELECT b.myNAME, b.name1, b.count1,
IF(@name = b.myNAME, @rank := @rank + 1, @rank := 1) -- 3.判断name是否等于此行的myNAME
AS rank, -- 如果是，则rank自增；如果不是，则初始化rank=1
@name := b.myNAME
FROM
(SELECT myNAME, name1, count1 FROM test
ORDER BY myNAME ASC, count1 DESC) b, -- 1.先进行子查询 排序
(SELECT @rownum := 0,
@name := NULL, -- 2.初始化rownum=0，name=NULL, rank=0
@rank := 0) a;
6.筛选组内前五（即行数小于等于5的行）
SELECT myNAME, name1, count1, rank
FROM
(SELECT b.myNAME, b.name1, b.count1,
IF(@name = b.myNAME, @rank := @rank + 1, @rank := 1) -- 3.判断name是否等于此行的myNAME
AS rank, #-- 如果是，则rank自增；如果不是，则初始化rank=1
@name := b.myNAME
FROM
(SELECT myNAME, name1, count1 FROM test
ORDER BY myNAME ASC, count1 DESC) b, -- 1.先进行子查询 排序
(SELECT @rownum := 0,
@name := NULL, -- 2.初始化rownum=0，name=NULL, rank=0
@rank := 0) a) result
WHERE rank <=5;
1.关联
SELECT *
FROM test a
JOIN test b
ON a.`myNAME` = b.`myNAME` AND a.`count1` <= b.`count1`;
2.排序
SELECT *
FROM test a
JOIN test b
ON a.`myNAME` = b.`myNAME` AND a.`count1` <= b.`count1`
ORDER BY a.`myNAME` ASC, a.`count1` DESC;
3.最终实现
SELECT a.myNAME,a.name1,a.count1,COUNT(*) AS rank
FROM test a
JOIN test b ON a.`myNAME` = b.`myNAME` AND a.`count1` <= b.`count1`
GROUP BY a.`myNAME`,a.`name1`,a.`count1`
ORDER BY a.`myNAME` ASC, a.`count1` DESC;
其实方法三只是方法二的补充，针对有数据相等的情况。
SELECT
a.myNAME,
a.name1,
a.count1,
COUNT(1) AS rank
FROM
test AS a,
test AS b
WHERE a.myNAME = b.myNAME
AND a.count1 < b.count1
OR (a.count1 = b.count1 AND a.myNAME <= b.myNAME)
GROUP BY a.myNAME, a.name1, a.count1
ORDER BY a.myNAME ASC, rank ASC;
END 2018-05-2510:54:26
原文：https://www.cnblogs.com/hider/p/9087374.html