刚才数据有一点错误。按下面这个:有这样一个表:tb
UID D1 D2
A001002 2005-08-12 16:41:13.357 2005-08-13 10:41:13.000
A001001 2005-08-12 23:59:59.000 2005-08-13 00:00:01.000
A001003 2005-08-13 13:59:59.000 2005-08-13 20:00:01.000
A001004 2005-08-14 13:59:59.000 2005-08-18 00:00:01.000要变成
UID D1 D2
A001002 2005-08-12 16:41:13.357 2005-08-12 23:59:59.999
A001002 2005-08-13 00:00:00.000 2005-08-13 10:41:13.000
A001001 2005-08-12 23:59:59.000 2005-08-12 23:59:59.999
A001001 2005-08-13 00:00:00.000 2005-08-13 00:00:01.000
A001003 2005-08-13 13:59:59.000 2005-08-13 20:00:01.000
A001004 2005-08-14 13:59:59.000 2005-08-14 23:59:59.999
A001004 2005-08-15 00:00:00.000 2005-08-15 23:59:59.999
A001004 2005-08-16 00:00:00.000 2005-08-16 23:59:59.999
A001004 2005-08-17 00:00:00.000 2005-08-17 23:59:59.999
A001004 2005-08-18 00:00:00.000 2005-08-18 00:00:01.000
UID D1 D2
A001002 2005-08-12 16:41:13.357 2005-08-13 10:41:13.000
A001001 2005-08-12 23:59:59.000 2005-08-13 00:00:01.000
A001003 2005-08-13 13:59:59.000 2005-08-13 20:00:01.000
A001004 2005-08-14 13:59:59.000 2005-08-18 00:00:01.000要变成
UID D1 D2
A001002 2005-08-12 16:41:13.357 2005-08-12 23:59:59.999
A001002 2005-08-13 00:00:00.000 2005-08-13 10:41:13.000
A001001 2005-08-12 23:59:59.000 2005-08-12 23:59:59.999
A001001 2005-08-13 00:00:00.000 2005-08-13 00:00:01.000
A001003 2005-08-13 13:59:59.000 2005-08-13 20:00:01.000
A001004 2005-08-14 13:59:59.000 2005-08-14 23:59:59.999
A001004 2005-08-15 00:00:00.000 2005-08-15 23:59:59.999
A001004 2005-08-16 00:00:00.000 2005-08-16 23:59:59.999
A001004 2005-08-17 00:00:00.000 2005-08-17 23:59:59.999
A001004 2005-08-18 00:00:00.000 2005-08-18 00:00:01.000
解决方案 »
- 如何将同一个存储过程返回集合合并
- sql2008中,如何同时复制多个表的表结构?
- 级链删除表的问题,分不多,真心求助
- 求助:sqlserver2005系统服务不能启动的问题
- 关于读取sqlserver 中image字段问题,急急急!!!
- 一个sql查询问题,感觉很难
- 请问应该使用事务还是使用锁 ???
- sql企业版乱码的问题
- 请教高手,看看我这怎么统计???
- linkedserver通过 odbc连接vfp!两台机器的系统是nt 4.0 和 netware!现在的问题是!可以建立连接但是看不到DBF文件!!该如何解决这个问题啊!
- 我想用Sql语句从存储过程中取得数据
- 高手进:求最值,并指出最值出现位置!
insert into #tb select 'A001002','2005-08-12 16:41:13.357','2005-08-13 10:41:13.000'
insert into #tb select 'A001001','2005-08-12 23:59:59.000','2005-08-13 00:00:01.000'
insert into #tb select 'A001003','2005-08-13 13:59:59.000','2005-08-13 20:00:01.000'
insert into #tb select 'A001004','2005-08-14 13:59:59.000','2005-08-18 00:00:01.000'select
a.UID,
D1 = (case b.id when 0 then convert(varchar(24),a.D1,121) else convert(char(10),dateadd(day,b.id,a.D1),120)+' 00:00:00.000' end),
D2 = (case b.id when datediff(day,a.D1,a.D2) then convert(varchar(24),a.D2,121) else convert(char(10),dateadd(day,b.id,a.D1),120)+' 23:59:59.999' end)
from
#tb a
inner join
(select 0 as id union select 1 union select 2 union select 3 union select 4) b
on
datediff(day,a.D1,a.D2) >= b.id
order by
a.UID,D1
可以将你的方法集体描述下吗?
Select @i = 1 ,@Time = '2004-12-31'Create table #acalendar (term smalldatetime)while @i < 366
Begin Select @S = 'IF Year('''+ Convert(char(10),@Time+@i,120)+''') <2006
Insert #aCalendar (Term) Values('''+Convert(char(10),@Time+@i,120) +''')'
Exec (@S)
Set @i = @i+1
EndGO
--- 插入测试数据
create table #tb(UID varchar(100),D1 datetime,D2 datetime,Be varchar(20) ,En varchar(20))
Insert Into #tb
Select 'A001002','2005-08-12 16:41:13.357','2005-08-13 10:41:13.000','00:00:00.000','23:59:59.0' union
Select 'A001001','2005-08-12 13:59:59.000','2005-08-13 00:00:01.000','00:00:00.000','23:59:59.0' union
Select 'A001003','2005-08-13 13:59:59.000','2005-08-13 20:00:01.000','00:00:00.000','23:59:59.0' union
Select 'A001004','2005-08-14 13:59:59.000','2005-08-18 00:00:01.000','00:00:00.000','23:59:59.0' union
Select 'A001005','2005-08-13 13:59:59.000','2005-08-25 00:00:01.000','00:00:00.000','23:59:59.0' GO
---- 查询 Select uid , a.D1 , a.D2
From #tb a
Where Datediff(Day,A.D1,A.D2) = 0
Union
Select uid , a.D1 , Cast(Convert(char(11),a.D1,120)+a.En as datetime) AS D2
From #tb a
Where Datediff(Day,A.D1,A.D2) > 0
Union
Select uid , Cast(Convert(char(11),a.D2,120)+a.Be as datetime) D1 , D2
From #tb a
Where Datediff(Day,A.D1,A.D2) > 0 UNION
Select A.UID , Cast(Convert(char(11),b.Term,120)+a.Be as datetime) d1 ,
Cast(Convert(char(11),b.Term,120)+a.en as datetime) d2
From #tb a, #aCalendar b
Where Datediff(Day,A.D1,A.D2) > 0
and B.Term Between a.D1 And A.D2-1