u010587476 于 2017.01.09 10:44 提问

sql分组后查询每个分组的最小值的一条记录(mysql)

CREATE TABLE `test` (
`pid` int(10) NOT NULL AUTO_INCREMENT,
`gid` int(11) NOT NULL,
`price` decimal(10,3) NOT NULL,
`other` decimal(10,3) NOT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

14个回答

engchina   2017.01.09 12:31

``````select s.pid,s.gid,s.price,s.other
from
(select min(price) as min_price,gid from test group by gid) t1,test s
where t1.min_price = s.price
and t1.gid = s.gid;
``````
engchina 回复engchina: 把表名改为test， select pid,gid,price,other from test where pid in ( select min(s.pid) as min_pid from (select min(price) as min_price,gid from test group by gid) t1,test s where t1.min_price = s.price and t1.gid = s.gid group by s.gid,s.price ) ;

engchina select pid,gid,price,other from sample where pid in ( select min(s.pid) as min_pid from (select min(price) as min_price,gid from test group by gid) t1,test s where t1.min_price = s.price and t1.gid = s.gid group by s.gid,s.price ) ;

engchina 回复深蓝色回忆: 假设你取pid最小的那个。

u010587476 不能去根据价格去比较吧！如果两个价格相同的话 我只取其中一个

qq_36978352   2017.01.09 11:04

select * from (
select t.*, row_number() over(partition by gid oredr by price) rnk from t t )
where rnk=1;

u010587476 但是使用的是mysql数据库 大神

HGoooonG   2017.01.09 11:06

select a.* from 表名 as a where price = (select min(price) from 表名 where a.gid=gid)

u010587476 根据价格去匹配的话 如果有两条价格一样的呢

u011368649   2017.01.09 11:08

SELECT * FROM 表 WHERE price IN(SELECT MIN(price) FROM 表 GROUP BY(gid));

u011368649 回复深蓝色回忆: 最后再按照gid聚合一次，不就可以筛选出来了？SELECT * FROM a WHERE price IN(SELECT MIN(price) FROM a GROUP BY(gid)) GROUP BY(gid);

u010587476 根据价格去匹配会不精确 如果有多个价格一样的要取出其中一条

Alina6   2017.01.09 11:27

qq_14843403 回复深蓝色回忆: 这两个字段有什么错误 ，我试了下 这样也可以正确查询出来啊，而且pid和other也是对应的值啊

u010587476 这样出来的经过会有点错误的，取出了gid分组和最低价 但是pid和other字段会有错误

engchina   2017.01.09 13:25

``````select pid,gid,price,other
from test
where pid in
(
select min(s.pid) as min_pid
from
(select min(price) as min_price,gid from test group by gid) t1,test s
where t1.min_price = s.price
and t1.gid = s.gid
group by s.gid,s.price
)
``````

``````select t2.pid,t2.gid,t2.price,t2.other
from test t2
where exists
(
select 1 from
(
select min(s.pid) as min_pid
from
(select min(price) as min_price,gid from test group by gid) t1,test s
where t1.min_price = s.price
and t1.gid = s.gid
group by s.gid,s.price
) t3
where t2.pid = t3.min_pid
)
``````
u010587476 谢谢 是可以的！ 如何效率更高吧

q1010174887   2017.01.09 10:52

mysql 函数 min 可以试一试？

u010587476   2017.01.09 10:58

u010587476   2017.01.09 11:24

qq_36978352   2017.01.09 11:28