mysql按照某个字段分组排序，然后取前面几条数据

建好表和插入数据

DROP TABLE IF EXISTS sec_ques;
CREATE TABLE sec_ques (
city varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
type varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
value smallint(2) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8；

INSERT INTO sec_ques VALUES (‘重庆’, ‘1’, 100);
INSERT INTO sec_ques VALUES (‘上海’, ‘1’, 100);
INSERT INTO sec_ques VALUES (‘北京’, ‘1’, 98);
INSERT INTO sec_ques VALUES (‘广州’, ‘1’, 96);
INSERT INTO sec_ques VALUES (‘湖南’, ‘1’, 93);

INSERT INTO sec_ques VALUES (‘重庆’, ‘2’, 90);
INSERT INTO sec_ques VALUES (‘上海’, ‘2’, 95);
INSERT INTO sec_ques VALUES (‘北京’, ‘2’, 97);
INSERT INTO sec_ques VALUES (‘广州’, ‘2’, 98);
INSERT INTO sec_ques VALUES (‘湖南’, ‘2’, 92);

INSERT INTO sec_ques VALUES (‘重庆’, ‘3’, 99);
INSERT INTO sec_ques VALUES (‘上海’, ‘3’, 100);
INSERT INTO sec_ques VALUES (‘北京’, ‘3’, 99);
INSERT INTO sec_ques VALUES (‘广州’, ‘3’, 99);
INSERT INTO sec_ques VALUES (‘湖南’, ‘3’, 99);

比如，查询出每个type值最大的前两条记录
测试：

第一种：
select * from sec_ques b where (select count(*) from sec_ques a where a.type=b.type and a.value>b.value)<2 order by type,value desc;

这种不完美，当表中存在多条value值相等，则会全部取出来。

结果如下：

第二种，当表中存在多条value值相等，则会只取一条出来。

sql如下：借助变量，将按照某个字段降序，并给该记录标记一个从大到小位置。

SELECT * FROM ( SELECT IF ( b.type =@type ,@rank :=@rank + 1 ,@rank := 1 ) AS rank, b.city,
b.type, b. VALUE, @type := b.type AS temtype FROM
( SELECT * FROM sec_ques ORDER BY type, VALUE DESC ) b,
(SELECT @type := NULL ,@rank := 1) c ) d WHERE d.rank <= 2