Mysql 分组排序

需求：

   按照cid、author分组，再按照id倒叙，取出test表中前2条记录出来。

  建表以及初始数据如下：

DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INT PRIMARY KEY,
cid INT,
author VARCHAR(30)
) ENGINE=INNODB;
 
 
INSERT INTO test VALUES
(1,1,'test1'),
(2,1,'test1'),
(3,1,'test2'),
(4,1,'test2'),
(5,1,'test2'),
(6,1,'test3'),
(7,1,'test3'),
(8,1,'test3'),
(9,1,'test3'),
(10,2,'test11'),
(11,2,'test11'),
(12,2,'test22'),
(13,2,'test22'),
(14,2,'test22'),
(15,2,'test33'),
(16,2,'test33'),
(17,2,'test33'),
(18,2,'test33');
INSERT INTO test VALUES (200,200,'200test_nagios');

解答：

   

SELECT
    id,
    cid,
    author
FROM
    (
        SELECT
            a.id,
            a.cid,
            a.author,
            CASE
        WHEN a.cid =@cid
        AND a.author =@aut THEN
            @x :=@x + 1
        ELSE
            @x := 1
        END AS 'tmp' ,@cid := a.cid ,@aut := a.author,
        @x AS r
    FROM
        test a,
        (
            SELECT
                @x := 1 ,@cid := '' ,@aut := ''
        ) b
    ORDER BY     a.cid,a.author
    ) tmp
WHERE  r <= 2