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

[半吊子攻城狮]

表结构 如下：

期望结果：


解释:根据gid 进行分组 查询每个gid分组下price最小值的** 一行记录**(即对应pid主键的一整条记录) sql怎么写

[半吊子攻城狮]

建表语句如下： 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

[中国风]

select * from (select *,@id:=if(`c_ID`=@gid,@id+1,1) as RN,@gid:=`gid` from table1 order by `gid`,`price`,`pid`) as t where RN=1 ;

[solofen]

SELECT * FROM (SELECT * FROM tab ORDER BY price) t GROUP BY t.gid;

[Abeta]

如果price最小值都只存在1行，可以这样：

select t1.pid,t2.gid,t2.price,t1.other from
(select gid,min(price) price from t1 group by gid) t2
left join t1 on t1.gid=t2.gid and t1.price=t2.price

[骑着蜗牛去爬山]

select * from tb tb1 where not exists(select 1 from tb tb2 where tb1.gid=tb2.gid and tb1.price<tb2.price)