問題:有n組數(shù)據(jù),要取出所有組數(shù)據(jù)中某個字段值最小的那一條記錄,最后得到一組數(shù)據(jù),這組數(shù)據(jù)表示的是每個組的最小的那一條數(shù)據(jù)的集合?
示例:
+--------+------------+-------+
| 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 |
+--------+------------+-------+
結(jié)果:
+--------+----------+-------+
| type | variety | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| orange | valencia | 3.59 |
| pear | bartlett | 2.14 |
| cherry | bing | 2.55 |
+--------+----------+-------+
注意:
不能直接group by 之后,取min值,這樣分組值和min值是對的,但是無法保證otherMsg是對的,會發(fā)生混亂
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.內(nèi)連接:查出每個組的最大值,然后連接查詢
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.查找某組數(shù)據(jù)中最小的2條數(shù)據(jù)
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.先分組按照價格降序排序,然后對結(jié)果集再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,然后將對應訂單數(shù)據(jù)
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
