22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(DATE bigint, A float, B float, C float, D float)
insert into #
select 201009140945, 12.35, 12.38, 12.35, 12.35 union all
select 201009140950, 12.35, 12.42, 12.35, 12.40 union all
select 201009140955, 12.40, 12.44, 12.40, 12.43 union all
select 201009141000, 12.43, 12.46, 12.42, 12.44 union all
select 201009141005, 12.46, 12.53, 12.46, 12.47 union all
select 201009141010, 12.47, 12.47, 12.46, 12.47
;with cte as
(
select row=row_number()over(order by DATE)-1, * from #
)
select
max(DATE)DATE,
A=max(case when row/3*3=row then A end),
B=max(B),
C=min(C),
D=max(case when row/3*3+2=row then D end)
from cte group by row/3
/*
DATE A B C D
-------------------- ---------------------- ---------------------- ---------------------- ----------------------
201009140955 12.35 12.44 12.35 12.43
201009141010 12.43 12.53 12.42 12.47
*/