mysql分组第⼀条_MySQL如何查询每个分组⾥第⼀条、最后
⼀条、最⼤⼀条的数据...
这篇⽂章将带着⼤家解决查询top N的数据，不仅仅是最⾼或者最低的⼀条。
⼀、准备⼯作
mysql删除重复的数据保留一条创建如下表并添加数据：
+--------+------------+-------+
| 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 |
+--------+------------+-------+
⼆、查询
1.查询每类⽔果⾥最便宜的
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
+--------+----------+-------+
和其他查询极值的sql⼀样分为两步：第⼀步到最符合条件的price，第⼆步查询符合这个条件其他列的数据。
⾃连接
1.出每类⽔果⾥价格最便宜的
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 |
+--------+----------+
2.通过查询相同表join上⼀步结果出其余列
pe, f.variety, f.price
from (
select type, min(price) as minprice
from fruits group by type
) as x inner join fruits as f pe = 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 pe = pe); +--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
+--------+----------+-------+
⼆、查询每组TOP N的⼏⾏数据
查询每类⽔果⾥最便宜的两个数据，这是第⼀次尝试
select type, variety, price
from fruits
where price = (select min(price) from fruits as f pe = pe)
or price = (select min(price) from fruits as f pe = pe
and price > (select min(price) from fruits as f2 pe = pe));
+--------+----------+-------+
| 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 |
+--------+----------+-------+
当查询TOP 3、TOP4..时这个⽅法会很难⽤
这⾥有⼀个跟好的⽅法：
select type, variety, price
from fruits
where (
select count(*) from fruits as f
pe = pe and f.price <= fruits.price
) <= 2;
这个⽅法更简洁、⽅便，但是它和上⾯的⽅法机制上是⼀样的，这两个从本质上来讲都是关于⽔果种类的⼆次计算。使⽤UNION
(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2) union all
(select * from fruits where type = 'cherry' order by price limit 2)需要注意的是：⽤union all⽽不是union

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。