mysql分组取数据_mysql中数据分组后取组内数据的最小值集合

问题:有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

);

参考博客: