由于业务提出新的需求,需要对数据库中的数据进行更改,数据结构大致如下:ikey--productid--startdate----enddate-----price
1-----89---------2006-1-1-----2006-12-31--560
2-----89---------2006-2-1-----2006-8-1----480
3-----89---------2006-5-1-----2006-10-1---550经过数据拆分后,结果如下:ikey--productid--startdate----enddate-----price
1-----89---------2006-1-1-----2006-2-1----560
2-----89---------2006-2-1-----2006-5-1----480
3-----89---------2006-5-1-----2006-8-1----550
4-----89---------2006-8-1-----2006-10-1---550
5-----89---------2006-10-1----2006-12-31--560
1-----89---------2006-1-1-----2006-12-31--560
2-----89---------2006-2-1-----2006-8-1----480
3-----89---------2006-5-1-----2006-10-1---550经过数据拆分后,结果如下:ikey--productid--startdate----enddate-----price
1-----89---------2006-1-1-----2006-2-1----560
2-----89---------2006-2-1-----2006-5-1----480
3-----89---------2006-5-1-----2006-8-1----550
4-----89---------2006-8-1-----2006-10-1---550
5-----89---------2006-10-1----2006-12-31--560
解决方案 »
- 怎么样判断访问者IP在我表内IP数据库的哪一行
- 无法绑定由多个部分组成的标识符
- sql語句問題
- 求一笔试 SQL 查询 语句
- 【帮人提问!!!Windows 2000 pro 能不能安装SQL 2000 server呀?】
- 用 xsl 把此 xml 与 此 html 结合, 这个xsl 如何写?(看了教程,但觉得好像xsl没那么自由灵活)
- 这两个sql语句怎么合成一句?
- 如何用SQL语句获得数据库中的表 的创建日期?
- 请各位指点一下,如何通过编写存储过程往一个已存在的视图里插入一新列。
- 我在对表作物理删除时,提示错误信息说:必须以独占方式打开表。我该怎么做?谢谢!
- 百分求解存储过程错误提示----在线等待
- SQL里面如何生成“流程图”?
create table #(ikey int identity(1,1),productid int,startdate datetime,enddate datetime,price numeric(20,6))
insert into #(productid,startdate,enddate,price)
select 89,'2006-01-01','2006-12-31',560
union all select 89,'2006-02-01','2006-08-01',480
union all select 89,'2006-05-01','2006-10-01',550create table #t1(dt datetime)
insert into #t1(dt)
select startdate from #
union all select enddate from #select identity(int,1,1) as ikey,t1.productid,t1.startdate,t1.enddate,t1.price
into #t2
from (select #.ikey,
#.productid,
#.startdate,
(select top 1 dt from #t1 where #t1.dt > #.startdate order by dt) as enddate,
#.price
from #
union all
select #.ikey,
#.productid,
#.enddate as startdate,
(select top 1 dt from #t1 where #t1.dt > #.enddate order by dt) as enddate,
(select t.price from # t where t.enddate = (select top 1 dt from #t1 where #t1.dt > #.enddate order by dt))
from #)t1
order by startdate
select * from #t2 where enddate is not nulldrop table #
drop table #t1
drop table #t2
union all select 2,89,'2006-2-1','2006-8-1',480
union all select 3,89,'2006-5-1','2006-10-1',550select * into #tmp from (select startdate dateT from @a
union all
select enddate datet from @a)a order by dateT
select dateT into #tmp1 from #tmp a where datet not in(select startdate from @a)select ikey,
productid,
startdate,
enddate=(select min(dateT) from @a b,#tmp c where startdate=a.startdate and b.startdate<c.datet),
price into #tmp2
from @a a
Union all
select id=(select count(1) from #tmp1 where datet<=a.datet)+(select max(ikey) from @a),
productid=(select min(productid) from @a),
startdate=datet,
enddate=(select min(dateT) from #tmp1 where datet>a.datet ),
price=(select price from @a b where startdate=(select max(startdate) from @a ))
from #tmp1 a
select * from #tmp2 where enddate is not null
drop table #tmp,#tmp1,#tmp2