在开发中经常遇到这样一类需求：取每种类型排名前几的数据。在此我简称它为组内排序。 以下，我借鉴了别人的方法并添加自己的想法，就这类问题做一下理解：
数据准备
代码语言：javascript复制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).其他类型以此类推
