MYSQL 实现 MSSQL row_number() 类似的分组排序

-- 键表

CREATE TABLE `tbl01` (
  `id` INT(11) NOT NULL,
  `pid` INT(11) DEFAULT NULL,
  `decs` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

-- 测试数据
INSERT INTO tbl01(id, pid, decs) VALUES (1, 1, 'A');
INSERT INTO tbl01(id, pid, decs) VALUES (2, 1, 'B');
INSERT INTO tbl01(id, pid, decs) VALUES (3, 1, 'C');
INSERT INTO tbl01(id, pid, decs) VALUES (4, 1, 'D');
INSERT INTO tbl01(id, pid, decs) VALUES (5, 1, 'E');
INSERT INTO tbl01(id, pid, decs) VALUES (11, 1, 'F');
INSERT INTO tbl01(id, pid, decs) VALUES (12, 1, 'G');
INSERT INTO tbl01(id, pid, decs) VALUES (13, 1, 'H');
INSERT INTO tbl01(id, pid, decs) VALUES (14, 1, 'I');
INSERT INTO tbl01(id, pid, decs) VALUES (15, 1, 'J');
INSERT INTO tbl01(id, pid, decs) VALUES (20, 1, 'K');
INSERT INTO tbl01(id, pid, decs) VALUES (31, 1, 'L');
INSERT INTO tbl01(id, pid, decs) VALUES (43, 1, 'M');
INSERT INTO tbl01(id, pid, decs) VALUES (56, 1, 'N');
INSERT INTO tbl01(id, pid, decs) VALUES (67, 1, 'P');

--  查询语句：

-- 1.  根据pid 分组查询

SELECT t1.*,

(SELECT COUNT(*)+1 FROM tbl01 

WHERE pid=t1.pid AND id<t1.id) AS g_id

FROM tbl01  t1

-- 2.  根据 PID分组，然后取出每组前3条纪录

SELECT T.* 
FROM
(
SELECT t1.*,
(SELECT COUNT(*)+1 FROM tbl01 WHERE pid=t1.pid AND id<t1.id) AS g_id
FROM tbl01 t1
) T
WHERE T.g_id<=3

-- 3.  排序字段为两个， 比如， 分组后， 按照id排序后， 再按照decs排序

SELECT t1.*,

(SELECT COUNT(*)+1 FROM tbl01 

WHERE pid=t1.pid AND (id<t1.id  or (id=t1.id and decs < t1.decs))) AS g_id

FROM tbl01  t1