mysql 排序与分组问题

Question

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

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;

Answer 1

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;

Answer 2

select c1.* from jx_goods c1 inner join jx_goods c2 on c1.cid=c2.cid and c1.market_price > c2.market_price;

你这个sql 应该会报错吧，group by 的时候不能用 * ，数据是以每一组数据的集合展示出来的

Answer 3

用视图也可以很好的解决问题
create algorithm = temptable view v_goods as select * from jx_goods order by market_price desc;
select * from v_goods group by cid;