请问如何把一条数据分解成多条数据？内详

Question

我想在query中把这样的一条数据
FromDate    ToDate      Price
01/10/2009  05/10/2009  100分解成
FromDate    ToDate      Price
01/10/2009  01/10/2009  100
02/10/2009  02/10/2009  100
03/10/2009  03/10/2009  100
04/10/2009  04/10/2009  100
05/10/2009  05/10/2009  100不知道大家道不知道用什么方法可以达成？先谢谢了

Answer 1

分解成 
FromDate    ToDate      Price 
01/10/2009  01/10/2009  100 
02/10/2009  02/10/2009  100 
03/10/2009  03/10/2009  100 
04/10/2009  04/10/2009  100 
05/10/2009  05/10/2009  100 
上面这种分解有什么意义呢？是否是分解成下面这样 
FromDate    ToDate      Price 
01/10/2009  02/10/2009  100 
02/10/2009  03/10/2009  100 
03/10/2009  04/10/2009  100 
04/10/2009  05/10/2009  100 

Answer 2

--> Title  : Generating test data [tb]
--> Author : wufeng4552
--> Date   : 2009-11-04 09:36:38;with wufeng4552(FromDate,ToDate)
as
(select cast('2009-10-01'as datetime) FromDate,cast('2009-10-01'as datetime)ToDate
 union all
 select dateadd(day,1,FromDate),dateadd(day,1,ToDate) 
 from wufeng4552 
 where dateadd(day,1,ToDate)<='2009-10-05'
)select * from wufeng4552
/*
FromDate                ToDate
----------------------- -----------------------
2009-10-01 00:00:00.000 2009-10-01 00:00:00.000
2009-10-02 00:00:00.000 2009-10-02 00:00:00.000
2009-10-03 00:00:00.000 2009-10-03 00:00:00.000
2009-10-04 00:00:00.000 2009-10-04 00:00:00.000
2009-10-05 00:00:00.000 2009-10-05 00:00:00.000(5 個資料列受到影響)
*/

Answer 3

--> Title  : Generating test data [tb]
--> Author : wufeng4552
--> Date   : 2009-11-04 09:36:38;with wufeng4552(FromDate,ToDate,Price)
as
(select cast('2009-10-01'as datetime) FromDate,cast('2009-10-01'as datetime)ToDate,100 Price
 union all
 select dateadd(day,1,FromDate),dateadd(day,1,ToDate) ,100 Price
 from wufeng4552 
 where dateadd(day,1,ToDate)<='2009-10-05'
)select * from wufeng4552
/*
FromDate                ToDate                  Price
----------------------- ----------------------- -----------
2009-10-01 00:00:00.000 2009-10-01 00:00:00.000 100
2009-10-02 00:00:00.000 2009-10-02 00:00:00.000 100
2009-10-03 00:00:00.000 2009-10-03 00:00:00.000 100
2009-10-04 00:00:00.000 2009-10-04 00:00:00.000 100
2009-10-05 00:00:00.000 2009-10-05 00:00:00.000 100(5 個資料列受到影響)*/

Answer 4

declare @startdate datetime,@enddate datetime
set @startdate='01/10/2009'
set @enddate='05/10/2009'select convert(varchar(10),dateadd(mm,number,@startdate),103)  as FromDate,
       convert(varchar(10),dateadd(mm,number,@startdate),103)  as ToDate,
       Price='100'
from
    master..spt_values 
where 
    datediff(day,dateadd(mm,number,@startdate), @enddate)>=0
    and number>=0 
    and type='p'
/*FromDate   ToDate     Price
---------- ---------- -----
10/01/2009 10/01/2009 100
10/02/2009 10/02/2009 100
10/03/2009 10/03/2009 100
10/04/2009 10/04/2009 100
10/05/2009 10/05/2009 100(5 行受影响)*/

Answer 5

declare @startdate datetime,@enddate datetime
set @startdate='01/10/2009'
set @enddate='05/10/2009'select convert(varchar(10),dateadd(mm,number,@startdate),101)  as FromDate,
       convert(varchar(10),dateadd(mm,number,@startdate),101)  as ToDate,
       Price='100'
from
    master..spt_values 
where 
    datediff(day,dateadd(mm,number,@startdate), @enddate)>=0
    and number>=0 
    and type='p'
/*FromDate   ToDate     Price
---------- ---------- -----
01/10/2009 01/10/2009 100
02/10/2009 02/10/2009 100
03/10/2009 03/10/2009 100
04/10/2009 04/10/2009 100
05/10/2009 05/10/2009 100(5 行受影响)
*/

Answer 6

/*
-- Author:SQL77--RICHIE 
-- Version:V1.001  Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null 
    Drop table TB
Go
Create table TB(FromDate smalldatetime,ToDate smalldatetime,Price int)
Go
Insert into TB
 select '01/10/2009','05/10/2009',100 
Go
 
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate))--Start
SELECT DISTINCT 
CONVERT(VARCHAR(10),
DATEADD(DD,NUMBER,
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate))),
103) TIME1, 
CONVERT(
VARCHAR(10),
DATEADD(DD,NUMBER,
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate))),103) TIME2,PRICE
FROM MASTER..SPT_VALUES M,
TB T WHERE 
DATEADD(DD,NUMBER,
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate)))
<=DATENAME(YY,CONVERT(DATETIME,ToDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,ToDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,ToDate)) AND M.TYPE='P'--Result:
/*
TIME1      TIME2      PRICE       
---------- ---------- ----------- 
01/10/2009 01/10/2009 100
02/10/2009 02/10/2009 100
03/10/2009 03/10/2009 100
04/10/2009 04/10/2009 100
05/10/2009 05/10/2009 100（所影响的行数为 5 行）*/
--End 

Answer 7

if exists(select * from sysobjects where name = 'tb')
begin
  drop table tb
endcreate table tb(FromDate  datetime,   ToDate  datetime,    Price  int)
insert into tb
select '01/10/2009',  '05/10/2009',  100 create table #tmp(FromDate  datetime,   ToDate  datetime,    Price  int)declare @i int
declare @int int
select @int = datediff(mm,FromDate,ToDate) from tb
select @i = 0while (@i <=@int )
begin
      insert into   #tmp
      select   dateadd(mm,@i,FromDate ), dateadd(mm,@i,FromDate ),Price from tb      set @i=@i+1
endselect *from #tmp=========
*FromDate   ToDate     Price
---------- ---------- -----
10/01/2009 10/01/2009 100
10/02/2009 10/02/2009 100
10/03/2009 10/03/2009 100
10/04/2009 10/04/2009 100
10/05/2009 10/05/2009 100(5 行受影响)*/

Answer 8

/*
-- Author:SQL77--RICHIE 
-- Version:V1.001  Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null 
    Drop table TB
Go
Create table TB(FromDate smalldatetime,ToDate smalldatetime,Price int)
Go
Insert into TB
 select '01/10/2009','05/10/2009',100 
Go
 --Start
SELECT DISTINCT 
CONVERT(VARCHAR(10),
DATEADD(DD,NUMBER,
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate))),
103) TIME1, 
CONVERT(
VARCHAR(10),
DATEADD(DD,NUMBER,
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate))),103) TIME2,PRICE
FROM MASTER..SPT_VALUES M,
TB T WHERE 
DATEADD(DD,NUMBER,
DATENAME(YY,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,FromDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,FromDate)))
<=DATENAME(YY,CONVERT(DATETIME,ToDate))+'-'+
DATENAME(DD,CONVERT(DATETIME,ToDate))+'-'+
DATENAME(MM,CONVERT(DATETIME,ToDate)) AND M.TYPE='P'--Result:
/*
TIME1      TIME2      PRICE       
---------- ---------- ----------- 
01/10/2009 01/10/2009 100
02/10/2009 02/10/2009 100
03/10/2009 03/10/2009 100
04/10/2009 04/10/2009 100
05/10/2009 05/10/2009 100（所影响的行数为 5 行）*/
--End 

Answer 9

DECLARE @TB TABLE([FromDate] DATETIME, [ToDate] DATETIME, [Price] INT)
INSERT @TB 
SELECT '01/10/2009', '05/10/2009', 100SET DATEFORMAT DMYSELECT DATEADD(DAY,NUMBER,FromDate) AS FromDate,
DATEADD(DAY,NUMBER,FromDate) AS ToDate,
Price
FROM @TB,(SELECT NUMBER FROM MASTER..SPT_VALUES WHERE TYPE='P') AS B
WHERE DATEDIFF(DAY,FromDate,ToDate)>=NUMBER/*
FromDate                                               ToDate                                                 Price       
------------------------------------------------------ ------------------------------------------------------ ----------- 
2009-10-01 00:00:00.000                                2009-10-01 00:00:00.000                                100
2009-10-02 00:00:00.000                                2009-10-02 00:00:00.000                                100
2009-10-03 00:00:00.000                                2009-10-03 00:00:00.000                                100
2009-10-04 00:00:00.000                                2009-10-04 00:00:00.000                                100
2009-10-05 00:00:00.000                                2009-10-05 00:00:00.000                                100
*/