要求,按品牌排序,并选出每个品牌的价格最高的记录
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;