mysql 查询top N (解释sql思路和语法)，分组查询排序前n行 的sql怎么写

 

遇到这个问题，我也不会，然后我就上网搜，发现篇篇都是复制的啊，太没意思了。

我坚持原创。拒绝天下统一互抄！

而且我会把解题思路告诉你们，以后再遇到类似问题，就懂了

我的文章就算是mysql小白也能够一分钟就读懂我写的！

前置：mysql 环境，导入数据

create table goods (
  goods_id mediumint(8) unsigned primary key auto_increment,
  goods_name varchar(120) not null default '',
  cat_id smallint(5) unsigned not null default '0',
  brand_id smallint(5) unsigned not null default '0',
  goods_sn char(15) not null default '',
  goods_number smallint(5) unsigned not null default '0',
  shop_price decimal(10,2) unsigned not null default '0.00',
  market_price decimal(10,2) unsigned not null default '0.00',
  click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8;
 
 
insert into goods values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),
(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
(8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);

 

mysql  查询top N ，分组查询排序前某行 的sql怎么写

题目：查询出每种类别，商品价格排名前3的商品都查出来

方法一的写法：比较法，找出值比其他值小，行数差小于3的

select goods_id,goods_name,cat_id,shop_price
 from goods
  where (select count(*) from goods g2 where goods.shop_price<g2.shop_price and goods.cat_id=g2.cat_id)<3 
  order by cat_id ,shop_price desc;

运行结果如下：

这个思路怎么去理解呢？首先假设有以下两种表，左边和右边都是一模一样的

如上图，想求出前3最大号码的，比右边小的行数<3就可以了。如此类推。

所以再看回那个sql，就能理解了吧同样道理，求最后小的3行，把>号改成<就可以了，order by 排序换一下就好了

select goods_id,goods_name,cat_id,shop_price
 from goods
  where (select count(*) from goods g2 where goods.shop_price>g2.shop_price and goods.cat_id=g2.cat_id)<3 
  order by cat_id ,shop_price;

---  求价格最小的倒数的3行

这个方法的缺陷/弊端：效率低，如果表有100行，每行要比较100次，一共是10000次。如果这个表有一千行，那就是一亿次了。。。。

 

 

方法二的写法：用左连接查询

select w1.goods_id,w1.goods_name,w1.cat_id,w1.shop_price from goods w1
left join goods w2 on w1.cat_id=w2.cat_id where w1.shop_price<=w2.shop_price 
group by w1.cat_id,w1.goods_name,w1.shop_price having count(*)<4;

--- 解释：先通过条件再关联了，少了部分笛卡尔积。还是比第一种方法要快一点。

sql的意思：两张表通过关联语句拼成一张表，w1.shop_price<=w2.shop_price 为啥是小于等于呢？这是个条件，如果是<号，则最大一行找不到匹配条件的了。count(*)<4为什么是小于4呢，因为having是对前面所有的结果，筛选出前面3条，所以是<4.

看下运行结果。。。如下

先通过条件再关联了，少了部分笛卡尔积。having count(*)<3表示行数比较差是小于3的。

还是比第一种方法要快一点。还有更快的方法吗？

 

方法三。通过函数rank()，执行效率高

不支持mysql 8.0以下的。所以。。。先略吧。等我服务升8.0再更新博客