现有表:
日期 数值
2008-01-01 100
2008-02-01 220
2008-02-21 100想做一个视图:
开始日期 结束日期 数值
2008-01-01 2008-01-31 100
2008-02-01 2008-02-20 220
2008-02-21 today 100
……说明:开始日期就是表中的日期,按开始日期排序,结束日期是下一行开始日期减1,
最后一行结束日期的值是当前系统日期。
谢谢!
日期 数值
2008-01-01 100
2008-02-01 220
2008-02-21 100想做一个视图:
开始日期 结束日期 数值
2008-01-01 2008-01-31 100
2008-02-01 2008-02-20 220
2008-02-21 today 100
……说明:开始日期就是表中的日期,按开始日期排序,结束日期是下一行开始日期减1,
最后一行结束日期的值是当前系统日期。
谢谢!
isnull(convert(varchar(10),b.结束日期-1,120),'today') as 结束日期,
a.数值
from tb as a
outer apply(
select top(1) 日期 as 结束日期
from tb
where 日期>a.日期
order by 日期
) as b
insert into tb values('2008-01-01' , 100 )
insert into tb values('2008-02-01' , 220 )
insert into tb values('2008-02-21' , 100 )
goselect rq1 = rq ,
rq2 = isnull(dateadd(day , -1 , (select top 1 rq from tb where rq > t.rq)),getdate()),
val
from tb tdrop table tb/*
rq1 rq2 val
------------------------------------------------------ ------------------------------------------------------ -----------
2008-01-01 00:00:00.000 2008-01-31 00:00:00.000 100
2008-02-01 00:00:00.000 2008-02-20 00:00:00.000 220
2008-02-21 00:00:00.000 2009-11-26 17:07:08.013 100(所影响的行数为 3 行)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-26 17:08:31
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (日期 datetime,数值 int)
INSERT INTO @tb
SELECT '2008-01-01',100 UNION ALL
SELECT '2008-02-01',220 UNION ALL
SELECT '2008-02-21',100--SQL查询如下:select convert(varchar(10),a.日期,120) as 开始日期,
isnull(convert(varchar(10),b.结束日期-1,120),'today') as 结束日期,
a.数值
from @tb as a
outer apply(
select top(1) 日期 as 结束日期
from @tb
where 日期>a.日期
order by 日期
) as b
/*
开始日期 结束日期 数值
---------- ---------- -----------
2008-01-01 2008-01-31 100
2008-02-01 2008-02-20 220
2008-02-21 today 100(3 行受影响)
*/
as
select 开始日期,结束日期=z.开始日期,数值
from (select rn=row_number() over(order by 开始日期),* from tb ) k
join (select rn=row_number() over(order by 开始日期),* from tb ) z
on k.id=z.id-1
INSERT INTO @tb
SELECT '2008-01-01',100 UNION ALL
SELECT '2008-02-01',220 UNION ALL
SELECT '2008-02-21',100
select 日期=convert(varchar(10),k.日期,120),结束日期=isnull(convert(varchar(10),z.日期,120),'today'),k.数值
from (select rn=row_number() over(order by 日期),* from @tb ) k
left join (select rn=row_number() over(order by 日期),* from @tb ) z
on k.rn=z.rn-1
/*
(3 行受影响)
日期 结束日期 数值
---------- ---------- -----------
2008-01-01 2008-02-01 100
2008-02-01 2008-02-21 220
2008-02-21 today 100
*/