Num Value Time
1 1.5 2009/05/01
1 3.5 2009/05/02
1 2.8 2009/05/03
1 1.5 2009/05/05
1 3 2009/05/06
2 2.1 2009/05/03
2 3.4 2009/05/04
2 3.5 2009/05/05
2 3.6 2009/05/06
2 2 2009/05/02
3 1.3 2009/05/03
3 1.9 2009/05/04
3 1.5 2009/05/05
3 1.3 2009/05/06
3 2.9 2009/05/04
5 3 2009/05/05
5 2.8 2009/05/06
5 2.9 2009/05/07
5 2.4 2009/05/08
6 2.3 2009/05/01
6 1.3 2009/05/02
6 3 2009/05/03
6 2.3 2009/05/04
6 3 2009/05/05
6 2.9 2009/05/10我想要的结果是1 3 2009/05/06
2 3.4 2009/05/04
5 3 2009/05/05
6 3 2009/05/03
1:按照Num分组
2:Time asc
3:Value大于3的第一条(如果没有大于3的值该Num就不要了)
1 1.5 2009/05/01
1 3.5 2009/05/02
1 2.8 2009/05/03
1 1.5 2009/05/05
1 3 2009/05/06
2 2.1 2009/05/03
2 3.4 2009/05/04
2 3.5 2009/05/05
2 3.6 2009/05/06
2 2 2009/05/02
3 1.3 2009/05/03
3 1.9 2009/05/04
3 1.5 2009/05/05
3 1.3 2009/05/06
3 2.9 2009/05/04
5 3 2009/05/05
5 2.8 2009/05/06
5 2.9 2009/05/07
5 2.4 2009/05/08
6 2.3 2009/05/01
6 1.3 2009/05/02
6 3 2009/05/03
6 2.3 2009/05/04
6 3 2009/05/05
6 2.9 2009/05/10我想要的结果是1 3 2009/05/06
2 3.4 2009/05/04
5 3 2009/05/05
6 3 2009/05/03
1:按照Num分组
2:Time asc
3:Value大于3的第一条(如果没有大于3的值该Num就不要了)
go
create table [tb]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
select 1,1.5,'2009/05/01' union all
select 1,3.5,'2009/05/02' union all
select 1,2.8,'2009/05/03' union all
select 1,1.5,'2009/05/05' union all
select 1,3,'2009/05/06' union all
select 2,2.1,'2009/05/03' union all
select 2,3.4,'2009/05/04' union all
select 2,3.5,'2009/05/05' union all
select 2,3.6,'2009/05/06' union all
select 2,2,'2009/05/02' union all
select 3,1.3,'2009/05/03' union all
select 3,1.9,'2009/05/04' union all
select 3,1.5,'2009/05/05' union all
select 3,1.3,'2009/05/06' union all
select 3,2.9,'2009/05/04' union all
select 5,3,'2009/05/05' union all
select 5,2.8,'2009/05/06' union all
select 5,2.9,'2009/05/07' union all
select 5,2.4,'2009/05/08' union all
select 6,2.3,'2009/05/01' union all
select 6,1.3,'2009/05/02' union all
select 6,3,'2009/05/03' union all
select 6,2.3,'2009/05/04' union all
select 6,3,'2009/05/05' union all
select 6,2.9,'2009/05/10'
select t1.[Num],t1.Value,min(t1.Time) Time from
(
select *,
(select count(*) from [tb] where Num=t.Num and Value>t.Value) rank
from [tb] t where value >= 3
)
t1
join
(
select Num,max(rank) rank from
(
select *,
(select count(*) from [tb] where Num=t.Num and Value>t.Value) rank
from [tb] t where value >= 3
) tt
group by Num
) t2
on t1.Num=t2.Num and t1.rank=t2.rank
group by t1.[Num],t1.Value
order by t1.[Num]Num Value Time
----------- --------------------------------------- -----------------------
1 3.0 2009-05-06 00:00:00.000
2 3.4 2009-05-04 00:00:00.000
5 3.0 2009-05-05 00:00:00.000
6 3.0 2009-05-03 00:00:00.000(4 行受影响)