日期:2014-05-18  浏览次数:20604 次

如何用ssis获得前一天数据
数据源结构为:
date nums
12-11 37
12-12 59
12-13 89

希望的结构为:
date nums nums of yeseterday
12-11 37
12-12 59 37
12-13 89 59

------解决方案--------------------
SQL code
if not object_id('tb') is null
    drop table tb
Go
Create table tb([date] nvarchar(5),[nums] int)
Insert tb
select N'12-11',37 union all
select N'12-12',59 union all
select N'12-13',89
Go
Select *,
       (select top 1 [nums] from tb where [date]<t.[date] order by [date] desc)[nums of yeseterday]
from tb t
/*
date  nums        nums of yeseterday
----- ----------- ------------------
12-11 37          NULL
12-12 59          37
12-13 89          59

(3 row(s) affected)
*/

------解决方案--------------------
SQL code
if OBJECT_ID('tb') is not null drop table tb
go
create table tb
(
   date varchar(10),
   nums int
)
insert into tb
select '12-11',37 union all
select '12-12',59 union all
select '12-13',89

;with cte as
(
   select ROW_NUMBER()over(order by date) as rn,* from tb
)
select a.date,a.nums,isnull(b.nums,'') as [nums of yeseterday] from cte a left join cte b on a.rn=b.rn+1
order by a.date
----------------
date    nums    nums of yeseterday
12-11    37    0
12-12    59    37
12-13    89    59