------------------------------------ -- Author: happyflsytone -- Date:2008-11-17 17:32:45 -------------------------------------- Test Data: TA IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA Go CREATE TABLE TA(BeginDate SMALLDATETIME,EndDate SMALLDATETIME) Go INSERT INTO TA SELECT '2009-1-25','2009-1-28' GO --Start SELECT dateadd(d,n, BeginDate) as d FROM TA a, (select 0 as n union all select 1 union select 2 union all select 3 union all select 4) b where dateadd(d,n, BeginDate) <= EndDate--Result: /*d ----------------------- 2009-01-25 00:00:00 2009-01-26 00:00:00 2009-01-27 00:00:00 2009-01-28 00:00:00(4 行受影响) */ --End
一条语句不可能。时间间隔大了,你写union啊。
select 0 as n union all select 1 union select 2 union all select 3 union all select 4-- 如果时间跨度大,就把这个接长点
declare @t table(BeginDate datetime,EndDate datetime) insert into @t select '2009-1-25','2009-1-28'select dateadd(dd,b.id,a.BeginDate) as date from @t a, (select (select count(1) from sysobjects where id<t.id) as id from sysobjects t) b where b.id<=datediff(dd,BeginDate,EndDate)/* date ------------------------------------------------------ 2009-01-25 00:00:00.000 2009-01-26 00:00:00.000 2009-01-27 00:00:00.000 2009-01-28 00:00:00.000 */
那可以两个union 到10的子集 进行乘运算
union再cross join,或连几个系统表,我宁原写循环,效率好多了。
select a.n+10*b.n as n from (select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) a ,(select 0 as n union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 ) b
select rn = identity(int, 0, 1) into #t from sysobjects; select m.BeginDate + t.rn from #t t, mytab m where m.BeginDate + t.rn <= m.EndDate; drop table #t;
不过说实话,楼主问时,我第一个想到的是select identity ... into ..然后dateadd,或者是union再join, 压根没想到count计数..脑子还是不够快。钻钻要顶。
create table FFFF ( tdate datetime ) declare @i int set @i=(select datediff(dd,'2009-1-25','2009-1-28')) select dateadd(dd,1,'2009-1-25') while @i>0 begin insert into FFFF select dateadd(dd,@i-1,'2009-1-25') set @i=@i-1 end select * from FFFF
if object_id('FFFF') is not null drop table FFFF create table FFFF ( tdate datetime ) declare @i int set @i=(select datediff(dd,'2009-1-25','2009-1-28')) select dateadd(dd,1,'2009-1-25') while @i>0 begin
insert into FFFF select dateadd(dd,@i-1,'2009-1-25') set @i=@i-1 if(@i=1) begin insert into FFFF select '2009-1-28' end end select * from FFFF
-- Author: happyflsytone
-- Date:2008-11-17 17:32:45
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(BeginDate SMALLDATETIME,EndDate SMALLDATETIME)
Go
INSERT INTO TA
SELECT '2009-1-25','2009-1-28'
GO
--Start
SELECT
dateadd(d,n, BeginDate) as d
FROM
TA a,
(select 0 as n union all select 1 union select 2 union all select 3 union all select 4) b
where dateadd(d,n, BeginDate) <= EndDate--Result:
/*d
-----------------------
2009-01-25 00:00:00
2009-01-26 00:00:00
2009-01-27 00:00:00
2009-01-28 00:00:00(4 行受影响)
*/
--End
如果时间跨度大,就把这个接长点
declare @t table(BeginDate datetime,EndDate datetime)
insert into @t select '2009-1-25','2009-1-28'select
dateadd(dd,b.id,a.BeginDate) as date
from
@t a,
(select (select count(1) from sysobjects where id<t.id) as id from sysobjects t) b
where
b.id<=datediff(dd,BeginDate,EndDate)/*
date
------------------------------------------------------
2009-01-25 00:00:00.000
2009-01-26 00:00:00.000
2009-01-27 00:00:00.000
2009-01-28 00:00:00.000
*/
from (select 0 as n union all select 1 union all select 2
union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8 union all select 9 ) a
,(select 0 as n union all select 1 union all select 2
union all select 3 union all select 4 union all select 5
union all select 6 union all select 7 union all select 8 union all select 9 ) b
select m.BeginDate + t.rn from #t t, mytab m where m.BeginDate + t.rn <= m.EndDate;
drop table #t;
压根没想到count计数..脑子还是不够快。钻钻要顶。
(
tdate datetime
)
declare @i int
set @i=(select datediff(dd,'2009-1-25','2009-1-28'))
select dateadd(dd,1,'2009-1-25')
while @i>0
begin
insert into FFFF select dateadd(dd,@i-1,'2009-1-25')
set @i=@i-1
end
select * from FFFF
create table FFFF
(
tdate datetime
)
declare @i int
set @i=(select datediff(dd,'2009-1-25','2009-1-28'))
select dateadd(dd,1,'2009-1-25')
while @i>0
begin
insert into FFFF select dateadd(dd,@i-1,'2009-1-25')
set @i=@i-1
if(@i=1)
begin
insert into FFFF select '2009-1-28'
end
end
select * from FFFF