[sql server、oracle] 分组取最大值最小值常用sql详解

小小编

2 年前

sqlserver2005前：

–分组取最大最小常用sql
–测试环境
if OBJECT_ID(‘tb’) is not null drop table tb;
go
create table tb(
col1 int,
col2 int,
Fcount int)
insert into tb
select 11,20,1 union all
select 11,22,1 union all
select 11,23,2 union all
select 11,24,5 union all
select 12,39,1 union all
select 12,40,3 union all
select 12,38,4
go
–查询
–1
select * from tb t where Fcount=(select max(Fcount)from tb where col1=t.col1)
–2
select * from tb t where not exists(select 1 from tb where col1=t.col1 and Fcount>t.Fcount)    –效率要高很多（lui2015-5-13注释）
–结果
/*
col1        col2        Fcount
———– ———– ———–
12          38          4
11          24          5

====================================================

【SQL Server 2005后推荐使用这种方式】

SQL Server 2005后之后，引入了row_number()函数，row_number()函数的分组排序功能使这种操作变得非常简单

分组取TOP数据是T-SQL中的常用查询，如学生信息管理系统中取出每个学科前3名的学生。这种查询在SQL Server 2005之前，写起来很繁琐，需要用到临时表关联查询才能取到。SQL Server 2005后之后，引入了row_number()函数，row_number()函数的分组排序功能使这种操作变得非常简单。下面是一个简单示例：

代码如下:

–1.创建测试表
create table #score
(
name varchar(20),
subject varchar(20),
score int
)
–2.插入测试数据
insert into #score(name,subject,score) values(‘张三’,’语文’,98)
insert into #score(name,subject,score) values(‘张三’,’数学’,80)
insert into #score(name,subject,score) values(‘张三’,’英语’,90)
insert into #score(name,subject,score) values(‘李四’,’语文’,88)
insert into #score(name,subject,score) values(‘李四’,’数学’,86)
insert into #score(name,subject,score) values(‘李四’,’英语’,88)
insert into #score(name,subject,score) values(‘李明’,’语文’,60)
insert into #score(name,subject,score) values(‘李明’,’数学’,86)
insert into #score(name,subject,score) values(‘李明’,’英语’,88)
insert into #score(name,subject,score) values(‘林风’,’语文’,74)
insert into #score(name,subject,score) values(‘林风’,’数学’,99)
insert into #score(name,subject,score) values(‘林风’,’英语’,59)
insert into #score(name,subject,score) values(‘严明’,’英语’,96)
–3.取每个学科的前3名数据
select * from
(
select subject,name,score,ROW_NUMBER() over(PARTITION by subject order by score desc) as num from #score
) T where T.num <= 3 order by subject
–4.删除临时表
truncate table #score
drop table #score

语法形式：ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
解释：根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

==========================================

===========================================

oracle 分组取最大值方式

select distinct id, to_char(First_value(STARTTIME) OVER (PARTITION BY id order by to_number(VALUE) desc),’yyyy-mm-dd hh24:mi:ss’) as STARTTIME,
First_value(ENNAME) OVER (PARTITION BY id order by to_number(VALUE) desc) as ENNAME,
First_value(VALUE) OVER (PARTITION BY id order by to_number(VALUE) desc) as maxvalue
from tab_obj_rtatt_data_old where
Upper(ltrim(rtrim(ENNAME))) =?
AND STARTTIME>=to_date(?,’YYYY-MM-DD HH24:MI:SS’)
AND STARTTIME<=to_date(?,’YYYY-MM-DD HH24:MI:SS’)