if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (a int,b int,c int,d int ,t datetime )
insert into tb1
select '100 ',' 100 ',' 50 ',' 40 ',' 2014-08-30 00:00:000 ' union all
select '90 ',' 90',' 30 ',' 20 ',' 2014-08-29 00:00:000 'union all
select '80 ',' 80',' 20 ',' 15 ',' 2014-08-28 00:00:000 'union all
select '70 ',' 70',' 15 ',' 14',' 2014-08-27 00:00:000 '
go
with cte as
(
select * ,ROW_NUMBER() over (order by t asc) as rn from tb1
)
select b.a, b.b+b.c+b .d -a.b-a.c-a.d as e ,b.t from cte a
left join cte b
on a .rn =b.rn-1 where b.a is not null and DATEDIFF (DAY ,a .t ,b .t )=1 order by b.t desc --a e t
--100 50 2014-08-30 00:00:00.000
--90 25 2014-08-29 00:00:00.000
--80 16 2014-08-28 00:00:00.000是这个么?
go
create table tb1 (a int,b int,c int,d int ,t datetime )
insert into tb1
select '100 ',' 100 ',' 50 ',' 40 ',' 2014-08-30 00:00:000 ' union all
select '90 ',' 90',' 30 ',' 20 ',' 2014-08-29 00:00:000 'union all
select '80 ',' 80',' 20 ',' 15 ',' 2014-08-28 00:00:000 'union all
select '70 ',' 70',' 15 ',' 14',' 2014-08-27 00:00:000 '
go
with cte as
(
select * ,ROW_NUMBER() over (order by t asc) as rn from tb1
)
select b.a, b.b+b.c+b .d -a.b-a.c-a.d as e ,b.t from cte a
left join cte b
on a .rn =b.rn-1 where b.a is not null and DATEDIFF (DAY ,a .t ,b .t )=1 order by b.t desc --a e t
--100 50 2014-08-30 00:00:00.000
--90 25 2014-08-29 00:00:00.000
--80 16 2014-08-28 00:00:00.000是这个么?
year() 和 month() datediff (month ,t1,t2)这些函数用一下,我就不写了,吃饭去了
if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (a int,b int,c int,d int ,t datetime )
insert into tb1
select '100 ',' 100 ',' 50 ',' 40 ',' 2014-08-30 00:00:000 ' union all
select '90 ',' 90',' 30 ',' 20 ',' 2014-08-29 00:00:000 'union all
select '80 ',' 80',' 20 ',' 15 ',' 2014-08-28 00:00:000 'union all
select '70 ',' 70',' 15 ',' 14',' 2014-08-27 00:00:000 'create table #tb
(
a int,
e int,
t datetime,
rn int
)insert into #tb select a,b+c+d as e,t,ROW_NUMBER() over(order by t desc) from tb1 select a.a-b.a as a, a.e-b.e as e,a.t from #tb a
inner join (select * from #tb where rn>1) b
on a.rn+1=b.rn
drop table #tb