MySQL 组内排序

在开发中经常遇到这样一类需求：取每种类型排名前几的数据。在此我简称它为组内排序。
以下，我借鉴了别人的方法并添加自己的想法，就这类问题做一下理解：

数据准备

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  `variety` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into test(type,variety,price)
values
('apple','gala',2.79),
('apple','fuji',0.24),
('apple','limbertwig',2.87),
('orange','valencia',3.59),
('orange','navel',9.36),
('pear','bradford',6.05),
('pear','bartlett',2.14),
('cherry','bing',2.55),
('cherry','chelan',6.33),
('cherry','own',1.03);

1.取每类最便宜的一种

1.1 子查询
select * from ((select * from test order by price asc) tmp) group by type;

1.2 UNION(类型少还能用，类型多或类型不定就算了吧)
(select * from test where type = 'apple' order by price asc limit 1)
UNION
(select * from test where type = 'orange' order by price asc limit 1)
UNION
(select * from test where type = 'pear' order by price asc limit 1)
UNION
(select * from test where type = 'cherry' order by price asc limit 1);

1.3 子查询 where (coung(*)) < 1

• select * from test a where (select count(*) from test b where a.type = b.type and b.price < a.price) = 0;
• select * from test a where (select count(*) from test b where a.type = b.type and b.price < a.price) < 1;

针对1.3的解读:
1).where (count(*)) = 0 以apple为例：

• test a 中 id = 1 price = 2.79, test b 取 type = apple 且价格比 2.79 小的个数为 0 个 //不成立因为 id = 2 price = 0.24 比 2.79 小, count(*) = 1;
• test a 中 id = 2 price = 0.24, test b 取 type = apple 且价格比 0.24 小的个数为 0 个 //成立因为在 type = apple 中没有一个 price 小于 0.24, count(*) = 0,其他类型以此类推;

2).where (count(*)) < 1 以apple为例：

• test a 中 id = 1 price = 2.79, test b 取 type = apple 且价格比 2.79 小的个数小于 1 个 //不成立因为 id = 2 price = 0.24 比 2.79 小, count(*) = 1;
• test a 中 id = 2 price = 0.24, test b 取 type = apple 且价格比 0.24 小的个数小于 1 个 //成立因为在 type = apple 中没有一个 price 小于 0.24, count(*) < 1;

3).其他类型以此类推

2.取每类最便宜的两种

2.1 找出每类水果价格最便宜的两个品种，这时子查询就不能用了

2.2 UNION(类型少还能用，类型多或类型不定就算了吧)
(select * from test where type = 'apple' order by price asc limit 2)
UNION
(select * from test where type = 'orange' order by price asc limit 2)
UNION
(select * from test where type = 'pear' order by price asc limit 2)
UNION
(select * from test where type = 'cherry' order by price asc limit 2);

2.3 子查询 where (coung()) < 2
select * from test a where (select count() from test b where a.type = b.type and b.price < a.price) < 2;

针对2.3的解读：
1).where (count(*)) < 2 以apple为例：

• test a 中 id = 1 price = 2.79, test b 取 type = apple 且价格比 2.79 小的个数小于 2 个 //成立因为 id = 2 price = 0.24 比 2.79 小, count(*) = 1 < 2;
• test a 中 id = 2 price = 0.24, test b 取 type = apple 且价格比 0.24 小的个数小于 2 个 //成立因为在 type = apple 中没有一个 price 小于 0.24, count(*) = 0 < 2;
• test a 中 id = 3 price = 2.87, test b 取 type = apple 且价格比 2.87 小的个数小于 2 个 //不成立因为在 type = apple 中 id = 1 price = 2.79 和 id = 2 price = 0.24 都小于 2.87, count(*) = 2 !< 2;

2).其他类型以此类推

总结一下：在 where 条件中关联本类型并 count(*) 的方法最为灵活，推荐使用这种方法来解此类问题。推荐方法并没有对每种类型中进行排序啊！说好的组内排序呢……，好吧在最外层套一个 order_by price asc 就好了，本文意在为这类问题提供解决方案，请不要在意这个细节，哈哈～