需求：

   按照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,    authorFROM    (        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    ) tmpWHERE  r <= 2