小小编
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后推荐使用这种方式】
–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’)
