Mysql和Oracl 分组取每组前N条记录

最新推荐文章于 2024-07-03 03:22:03 发布

白马不是马

最新推荐文章于 2024-07-03 03:22:03 发布

阅读量386

点赞数

分类专栏： mysql java oracle 文章标签： mysql

java 同时被 3 个专栏收录

5 篇文章

订阅专栏

mysql

2 篇文章

订阅专栏

oracle

1 篇文章

订阅专栏

MySQL取每组的前N条记录：使用自连接的方式

一、对分组的记录取前N条记录：例子：取前 2条最大(小)的记录

1.用子查询：
SELECT * FROM right2 a  WHERE 2>
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account>a.account)
ORDER BY a.id,a.account DESC
2.用exists半连接：
SELECT * FROM right2 a  WHERE EXISTS
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account<b.account HAVING COUNT(*)<2)
ORDER BY a.id,a.account DESC
同理可以取组内最小的N条记录：
SELECT * FROM right2 a  WHERE 2>
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account<a.account)
ORDER BY a.id,a.account DESC
用exists：
SELECT * FROM right2 a  WHERE EXISTS
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account>b.account HAVING COUNT(*)<2)
ORDER BY a.id,a.account DESC
SQLServer支持top-N：
select a.* from tb a where val = (select top 3 val from tb where name = a.name) order by a.name

如果取每组的最大(小)一条记录我常用：

select id,val from t b inner join(select * from t a where  order by val desc) a  on a.id=b.id group by a.id order  by id;

二.实例：取每组最大的前 N条

create table t2 (
  id int primary key,       
  gid char, 
  col1 int, 
  col2 int  
) engine=innodb; 
insert into tx01 values
(1,'A',31,6), 
(2,'B',25,83), 
(3,'C',76,21), 
(4,'D',63,56), 
(5,'E',3,17), 
(6,'A',29,97), 
(7,'B',88,63), 
(8,'C',16,22), 
(9,'D',25,43), 
(10,'E',45,28), 
(11,'A',2,78), 
(12,'B',30,79), 
(13,'C',96,73), 
(14,'D',37,40), 
(15,'E',14,86), 
(16,'A',32,67), 
(17,'B',84,38), 
(18,'C',27,9), 
(19,'D',31,21), 
(20,'E',80,63), 
(21,'A',89,9), 
(22,'B',15,22), 
(23,'C',46,84), 
(24,'D',54,79), 
(25,'E',85,64), 
(26,'A',87,13), 
(27,'B',40,45), 
(28,'C',34,90), 
(29,'D',63,8), 
(30,'E',66,40), 
(31,'A',83,49), 
(32,'B',4,90), 
(33,'C',81,7), 
(34,'D',11,12), 
(35,'E',85,10), 
(36,'A',39,75), 
(37,'B',22,39), 
(38,'C',76,67), 
(39,'D',20,11), 
(40,'E',81,36); 
create table tx01 (
  id int primary key, 
  gid char, 
  col1 int, 
  col2 int  
) engine=innodb;

取每组gid 最大的前N条记录：使用自连接或则半连接：

*N=1时：

自连接：降序排好后group by取每组最大的一条。

select * from (select * from t2 order by col2 desc)as a group by gid order by gid;

半连接方式：找不到比最大值还大的。

select * from t2 a where not exists(select 1 from t2 b where b.gid=a.gid and b.col2>a.col2) order by a.gid;

*N=3时：

自连接：

select * from t2 a where 3>(select count(*) from t2 where gid=a.gid and col2>a.col2) order by a.gid,a.col2 desc;

半连接：

select * from t2 a where exists(select count(*) from t2 b where b.gid=a.gid and a.col2<b.col2 having(count(*))<3) order by a.gid,a.col2 desc

Oracle取每组的前N条记录:可以使用分析函数，hive中也能使用

SELECT * FROM(
SELECT z.type , z.code ,ROW_NUMBER()
OVER(PARTITION BY z.type ORDER BY z.code desc) AS code_id
FROM group_info z
)
WHERE code_id <4; 取每组最大的前四条记录