表A的定义:列1(关键字) 列2
001 2006-01-22
001 2006-02-03
001 2006-02-04
001 2006-02-05
001 2006-05-30
如何用一条SQL拆分为下表?列1(关键字) 列2 列3
001 2006-01-22 2006-02-03
001 2006-02-03 2006-02-04
001 2006-02-04 2006-02-05
001 2006-02-05 2006-05-30
001 2006-05-30 NULL
001 2006-01-22
001 2006-02-03
001 2006-02-04
001 2006-02-05
001 2006-05-30
如何用一条SQL拆分为下表?列1(关键字) 列2 列3
001 2006-01-22 2006-02-03
001 2006-02-03 2006-02-04
001 2006-02-04 2006-02-05
001 2006-02-05 2006-05-30
001 2006-05-30 NULL
drop table tab
go
if object_id('pubs..tab1') is not null
drop table tab1
go
if object_id('pubs..tab2') is not null
drop table tab2
gocreate table tab
(
列1 varchar(10),
列2 varchar(10)
)insert into tab(列1,列2) values('001','2006-01-22')
insert into tab(列1,列2) values('001','2006-02-03')
insert into tab(列1,列2) values('001','2006-02-04')
insert into tab(列1,列2) values('001','2006-02-05')
insert into tab(列1,列2) values('001','2006-05-30')select id=identity(int,1,1) , * into tab1 from tab order by 列1 , 列2
select id=identity(int,1,1) , * into tab2 from tab order by 列1 , 列2 select a.列1,a.列2,b.列2 as 列3 from tab1 a , tab2 b
where a.id = b.id - 1
union all
select 列1,max(列2) , 列3 = null from tab2 group by 列1drop table tab
drop table tab1
drop table tab2--结果
列1 列2 列3
---------- ---------- ----------
001 2006-01-22 2006-02-03
001 2006-02-03 2006-02-04
001 2006-02-04 2006-02-05
001 2006-02-05 2006-05-30
001 2006-05-30 NULL(所影响的行数为 5 行)
col1 varchar(10),
col2 datetime
)
insert A select '001', '2006-01-22'
union all select '001', '2006-02-03'
union all select '001', '2006-02-04'
union all select '001', '2006-02-05'
union all select '001', '2006-05-30'create function F_getCol(@col1 varchar(10), @col2 datetime)
returns datetime
as
begin
declare @col3 datetime select @col3=min(col2) from A where col1=@col1 and col2>@col2
return @col3
endselect col1, col2=col2, col3=dbo.F_getCol(col1, col2) from A