mysql分组查询取前几条

最近对mysql分组查询取前几条这个问题做个总结
1：建表，搞点测试数据
CREATE TABLE IF NOT EXISTS test (
id tinyint unsigned NOT NULL AUTO_INCREMENT,
type char(1) NOT NULL,
data char(4) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO test (id, type, data) VALUES
(1, ‘1’, ‘1qaz’),
(2, ‘1’, ‘2wsx’),
(3, ‘1’, ‘3edc’),
(4, ‘2’, ‘4rfv’),
(5, ‘3’, ‘5tgb’),
(6, ‘3’, ‘6yhn’),
(7, ‘4’, ‘1ujm’),
(8, ‘4’, ‘2mju’),
(9, ‘4’, ‘3nhy’),
(10, ‘4’, ‘4bgt’);
现在取type相同的前两条数据即：
(3, ‘1’, ‘3edc’),
(2, ‘1’, ‘2wsx’),
(4, ‘2’, ‘4rfv’),
(6, ‘3’, ‘6yhn’),
(5, ‘3’, ‘5tgb’),
(10, ‘4’, ‘4bgt’);
(9, ‘4’, ‘3nhy’),
开始测试：
第一种方法：
select a.type,a.data,b.data,count(a.data) from test a left join test b
on a.type=b.type and a.data<=b.data
group by a.type,a.data
– having count(a.data)<=2

这里为什么要将count(a.data)和a.data<=b.data这个标为黑色，看图，很完美的使用了聚合函数与分组与having 筛选。
之后只需要将count(a.data)为3的排除即可如图：

之后再加工下就可以了。
select a1.* from test a1
inner join
(select a.type,a.data from test a left join test b
on a.type=b.type and a.data<=b.data
group by a.type,a.data
having count(a.data)<=2
)b1
on a1.type=b1.type and a1.data=b1.data
order by a1.type,a1.data desc

第二种方法：
利用row_number或者说是制造一个row number
set @num := 0, @type := ”;
SELECT id, type, data,
case when @type = type
then @num:=@num+1
else @num:=1
end rownum,
@type:=type other_type
FROM test
order by type, data desc

黑色为重点。
这里可以看到和先前一样只要处理rownum为3的即可
set @num := 0, @type := ”;
select b.rownum,a.*
from test a
inner join
(SELECT type, data,
case when @type = type
then @num:=@num+1
else @num:=1
end rownum,
@type:=type other_type
FROM test
order by type, data desc
) b on b.type=a.type
and b.data=a.data where b.rownum<=2;

第三种方法：
是在一个国外的网站看到的，很好的解决方式，没有表间的连接查询，只有一张表。也是利用row number
set @num := 0, @type := ”;
select id, type, data,
@num := if(@type=type, @num + 1, 1) as row_number,
@type := type as other_type
from test c
order by type, data desc

好了也是和上面一样将rownumber为3的解决掉
set @num := 0, @type := ”;
select *
from (
select id,type, data,
@num := if(@type=type, @num + 1, 1) as row_number,
@type := type as other_type
from test c
order by type, data desc
) a
group by type, data
having row_number <= 2
– order by type, data desc;

是不是觉得结果不对，将order by type, data desc;去掉注释即可。哈哈
希望对你有帮助。