mysql 实现分组排序

CREATE TABLE `tem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;

INSERT INTO `tem`(`id`, `str`) VALUES (1, 'A');
INSERT INTO `tem`(`id`, `str`) VALUES (2, 'B');
INSERT INTO `tem`(`id`, `str`) VALUES (3, 'A');
INSERT INTO `tem`(`id`, `str`) VALUES (4, 'C');
INSERT INTO `tem`(`id`, `str`) VALUES (5, 'A');
INSERT INTO `tem`(`id`, `str`) VALUES (6, 'C');
INSERT INTO `tem`(`id`, `str`) VALUES (7, 'B');
    

SELECT
    @num := IF(@str = str, @num + 1, 1) num,
    id,
    @str := str str
FROM
    tem, (SELECT @str := '', @num := 0) t1
ORDER BY
    str, id;