MySQL 组内排序

lukachen

发布于 2023-10-22 15:26:37

1090

发布于 2023-10-22 15:26:37

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

数据准备

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).其他类型以此类推