mysql分组取每组前几条记录(排名)

1.创建表
create table tb(
name varchar(10),
val int,
memo varchar(20)
);

2.插入数据
insert into tb values('a', 2, 'a2(a的第二个值)');
insert into tb values('a', 1, 'a1--a的第一个值');
insert into tb values('a', 3, 'a3:a的第三个值');
insert into tb values('b', 1, 'b1--b的第一个值');
insert into tb values('b', 3, 'b3:b的第三个值');
insert into tb values('b', 2, 'b2b2b2b2');
insert into tb values('b', 4, 'b4b4');
insert into tb values('b', 5, 'b5b5b5b5b5');

3.按name分组取val最大的值所在行的数据

# 方法4：
SELECT a.* FROM tb AS a 
INNER JOIN 
(SELECT name , max(val) val FROM tb GROUP BY name) AS b 
ON a.name = b.name
AND a.val = b.val 
ORDER BY a.name ;

--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name ;
--方法2： 
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val) ;
--方法3： 
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name ;
--方法4： 
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name ;
--方法5 
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name ;

4.按name分组取val最小的值所在行的数据

--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name ;
--方法2： 
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val) ;
--方法3： 
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name ;
--方法4： 
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name ;
--方法5 
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name ;

5.按name分组取最小的两个(N个)val

select a.* from tb a 
where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) 
order by a.name ;

6.按name分组取最大的两个(N个)val

select a.* from tb a 
where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) 
order by a.name 

创建表

CREATE TABLE `mygoods` (  
  `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `cat_id` int(11) NOT NULL DEFAULT '0',  
  `price` tinyint(3) NOT NULL DEFAULT '0',  
  `status` tinyint(3) DEFAULT '1',  
  PRIMARY KEY (`goods_id`),  
  KEY `icatid` (`cat_id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  

插入数据

INSERT INTO `mygoods` VALUES (1, 101, 90, 0);  
INSERT INTO `mygoods` VALUES (2, 101, 99, 1);  
INSERT INTO `mygoods` VALUES (3, 102, 98, 0);  
INSERT INTO `mygoods` VALUES (4, 103, 96, 0);  
INSERT INTO `mygoods` VALUES (5, 102, 95, 0);  
INSERT INTO `mygoods` VALUES (6, 102, 94, 1);  
INSERT INTO `mygoods` VALUES (7, 102, 93, 1);  
INSERT INTO `mygoods` VALUES (8, 103, 99, 1);  
INSERT INTO `mygoods` VALUES (9, 103, 98, 1);  
INSERT INTO `mygoods` VALUES (10, 103, 97, 1);  
INSERT INTO `mygoods` VALUES (11, 104, 96, 1);  
INSERT INTO `mygoods` VALUES (12, 104, 95, 1);  
INSERT INTO `mygoods` VALUES (13, 104, 94, 1);  
INSERT INTO `mygoods` VALUES (15, 101, 92, 1);  
INSERT INTO `mygoods` VALUES (16, 101, 93, 1);  
INSERT INTO `mygoods` VALUES (17, 101, 94, 0);  
INSERT INTO `mygoods` VALUES (18, 102, 99, 1);  
INSERT INTO `mygoods` VALUES (19, 105, 85, 1);  
INSERT INTO `mygoods` VALUES (20, 105, 89, 0);  
INSERT INTO `mygoods` VALUES (21, 105, 99, 1);  

表mygoods为商品表，cat_id为分类id，goods_id为商品id，status为商品当前的状态位（1:有效，0:无效）

sql查询语句

-- 每个分类找出价格最高的两个商品
select a.* from mygoods a   
where (select count(*) from mygoods where cat_id = a.cat_id and price > a.price  ) <3
order by a.cat_id,a.price desc;
    
-- 每个分类找出价格最高的有效的两个商品（正确）   
select a.* from mygoods a   
where (select count(*) from mygoods where cat_id = a.cat_id and price > a.price and status=1  ) <3
and status=1
order by a.cat_id, a.price desc ;
        
-- 可以将每个分组下的goods_id合并
select cat_id,GROUP_CONCAT(goods_id) from mygoods group by cat_id;

-- 每个分类找出价格最高的商品
select a.* from mygoods a where price = (select max(price) from mygoods where cat_id=a.cat_id) order by a.cat_id;

-- 每个分类找出价格最低的商品
select a.* from mygoods a where price = (select min(price) from mygoods where cat_id=a.cat_id) order by a.cat_id;

转载于:https://www.cnblogs.com/apollo1616/p/10406212.html