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

表结构 以及数据如上

期望结果如下：

解释:根据gid 进行分组 查询每个gid分组下price最小值的** 一行记录**(即对应pid主键的一整条记录) 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

后来才看见你的建表语句，如果为表名test的话

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;

mysql 函数 min 可以试一试？

知道用min啊 最终要的是一整条记录 不是一两个字段

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

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

这个应该使用子查询吧，我写了一个，你可以试试
SELECT * FROM 表 WHERE price IN(SELECT MIN(price) FROM 表 GROUP BY(gid));

不能根据价格去判断 如果有价格相同呢

用 row_number() over()排序，把1的选出来就可以了

用的是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

假设你的表明为 sample

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

在这整形回复下，这是用in的写法

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
)

如果用exits写法

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
)