问题:有n组数据,要取出所有组数据中某个字段值最小的那一条记录,最后得到一组数据,这组数据表示的是每个组的最小的那一条数据的集合?
示例:
+--------+------------+-------+
| 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 |
+--------+----------+-------+
注意:
不能直接group by 之后,取min值,这样分组值和min值是对的,但是无法保证otherMsg是对的,会发生混乱
select type, min(price) as minprice,otherMsg
from fruits group by type
官方文档推荐的解决方案
1.使用一个不相关的子查询(Uncorrelated subquery)
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
2.LEFT JOIN:自连接
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
当 s1.price时最大值的时候,s2.article必然是null
查看博客的解决方案
1.内连接:查出每个组的最大值,然后连接查询
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;
2.子查询
select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type);
3.查找某组数据中最小的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;
4.使用 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)
5.先分组按照价格降序排序,然后对结果集再group by分组,让MySQL自动取第一个(存疑,性能问题+group by取值的随机性,虽然目前测试后确实是取的第一个,但官方文档没有说明group by是取第一个不保证日后这个机制还有效果)
SELECT *
FROM (SELECT *
FROM shop
[WHERE conditions]
ORDER BY price DESC) AS s
GROUP BY article
6.本人使用的方法:查找到student_id,grade_id分组后得到的最大值count,然后将对应订单数据
SELECT o.`garde_id`, o.`student_id`, o.`count`,o.id
FROM `wlx_order` o
WHERE (o.`garde_id`, o.`student_id`, o.`count`) IN (SELECT `garde_id`, `student_id`, MAX(count)
FROM `wlx_order`
GROUP BY `garde_id`, `student_id`)
AND o.`count` > 0
AND o.`mark` = 1
AND o.`status` = 'NORMAL';
查询回访记录中的最近时间的一个
select * from `wlx_visit_record` vc,`wlx_intention_student` is1
where is1.`id`=vc.`intention_student_id` and vc.`creation_date` =(
select MAX(vc2.`creation_date`) from `wlx_visit_record` vc2 where vc2.`intention_student_id`=is1.id
);
参考博客:
https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/
https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html
网友评论