首页 > 开发 > MySQL > 正文

mysql 排序与分组问题

2017-09-08 08:50:39  来源:网友分享

要求,按品牌排序,并选出每个品牌的价格最高的记录

create table if not exists jx_goods(
    id int unsigned primary key auto_increment,
    cid int unsigned,
    name varchar(30),
    market_price int unsigned,
    ads varchar(200)
);

insert into jx_goods values
    (null,1,'小米note',2999,'一面科技,一面艺术'),
    (null,1,'小米mix',3699,'不只是一块屏幕,而是通往未来世界的门'),
    (null,2,'华为nova',2599,'我的手机,我漂亮'),
    (null,2,'华为p9',3888,'瞬间,定格视界的角度'),
    (null,3,'三星Note7',6488,'炸天'),
    (null,3,'三星s7 edge',5888,'不一样的色彩'),
    (null,4,'ipone7',5388,'7,在此'),
    (null,4,'ipone7 plus',6388,'不只是大');

为什么以下的两种操作执行结果一样,并没有按要求进行排序操作,mysql版本是5.7.1 ,会不会是版本太高

select * from (select * from jx_goods order by market_price desc) as jx group by cid;
select * from (select * from jx_goods order by market_price asc) as jx group by cid;

解决方案

1.select a.* from jx_goods as a, (select cid, max(market_price) as max_market_price from jx_goods group by cid) as b where a.market_price = b.max_market_price and a.cid = b.cid order by a.cid;
2.select * from jx_goods where (cid, market_price) in (select cid, max(market_price) as market_price from jx_goods group by cid) order by cid;