这里有一个常见的SQL问题: 如何找到当前的每个程序的最新的日志? 如何从不同类别中找到最常使用的数据?等等. 通常情况这类问题可以被简化为"从不同的类别中选取特定的数据". 这里假设数据库的表结构及其数据如下所示:
+--------+------------+-------+ | type | variety | price | +--------+------------+-------+ | 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 | +--------+------------+-------+这里我们先把我们所期望的结果放在这里
+--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 |当然了,要得到这问题的答案有很多种.这里用两种来解释一下. 一种常见的方式是self-join,
首先根据不同的类别进行分组, select type, min(price) as minprice from fruits group by type; +--------+----------+ | type | minprice | +--------+----------+ | apple | 0.24 | | cherry | 2.55 | | orange | 3.59 | | pear | 2.14 | +--------+----------+ 第二部是将查询得到的数据重新连接到相同的table中;因为第一部分的查询已经分组了,因此需要将其放在子查询中并将其连接到table中 select f.type, f.variety, f.price from ( select type, min(price) as minprice from fruits group by type ) as x inner join fruits as f on f.type = x.type and f.price = x.minprice; +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | cherry | bing | 2.55 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 |这里还有一种方式是使用相关子查询.从效率上将可能有第一点,但是她更加清晰.
select type, variety, price from fruits where price = (select min(price) from fruits as f where f.type = fruits.type); +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | fuji | 0.24 | | orange | valencia | 3.59 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 |通过了子查询\相关子查询我们可以比较轻松的从大量数据中获取每一种类的数据.接下来我们更进一步
通常情况下各种数据库都提供了诸如MAX() MIN()这样的聚合函数来返回单独的一行数据.但是我们这里需要返回多条数据.先来看看第一种实现:
select type, variety, price from fruits where price = (select min(price) from fruits as f where f.type = fruits.type) or price = (select min(price) from fruits as f where f.type = fruits.type and price > (select min(price) from fruits as f2 where f2.type = fruits.type)); +--------+----------+-------+ | type | variety | price | +--------+----------+-------+ | apple | gala | 2.79 | | apple | fuji | 0.24 | | orange | valencia | 3.59 | | orange | navel | 9.36 | | pear | bradford | 6.05 | | pear | bartlett | 2.14 | | cherry | bing | 2.55 | | cherry | chelan | 6.33 | +--------+----------+-------+可以看到其实思路很简单,这里用SQL的伪代码来表示一下其实现方式
select type, variety, price from fruits where price = 价钱最低的 OR price = 价钱最低的 AND price > 价钱最低的这种方式可以实现,但是可拓展性比较差.如果现在需要查询TOP 3,TOP 4 怎么办?这里还有一种方式实现查询TOP 2 TOP 2 其实质就是 : 价格小于等于第二个便宜的产品.
select type, variety, price from fruits where ( select count(*) from fruits as f where f.type = fruits.type and f.price <= fruits.price ) <= 2;