NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
有一个表三列,最后一列如上所示,是datetime类型的字段,有没有方法能变成下面的样子呢?请大家指点一下!谢谢!
2007-01-20 13:20:00.000
2007-01-20 13:25:00.000
2007-01-20 13:30:00.000
2007-01-20 13:35:00.000
2007-01-20 13:40:00.000
2007-01-20 13:45:00.000
2007-01-20 13:50:00.000
2007-01-20 13:55:00.000
2007-01-20 14:00:00.000
分钟加五分钟,能不能实现?
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:20:00.000
有一个表三列,最后一列如上所示,是datetime类型的字段,有没有方法能变成下面的样子呢?请大家指点一下!谢谢!
2007-01-20 13:20:00.000
2007-01-20 13:25:00.000
2007-01-20 13:30:00.000
2007-01-20 13:35:00.000
2007-01-20 13:40:00.000
2007-01-20 13:45:00.000
2007-01-20 13:50:00.000
2007-01-20 13:55:00.000
2007-01-20 14:00:00.000
分钟加五分钟,能不能实现?
create table T(col1 int, col2 int, col3 datetime)
insert T select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
union all select NULL, 0, '2007-01-20 13:20:00.000'
select ID=identity(int, 0, 1),* into #T from Tselect col1, col2, col3=dateadd(minute, ID*5, col3)
from #T--result
col1 col2 col3
----------- ----------- ------------------------------------------------------
NULL 0 2007-01-20 13:20:00.000
NULL 0 2007-01-20 13:25:00.000
NULL 0 2007-01-20 13:30:00.000
NULL 0 2007-01-20 13:35:00.000
NULL 0 2007-01-20 13:40:00.000
NULL 0 2007-01-20 13:45:00.000
NULL 0 2007-01-20 13:50:00.000
NULL 0 2007-01-20 13:55:00.000
NULL 0 2007-01-20 14:00:00.000
`这样行不行?`我不太会用这个SQL SERVER`
--將原表數據復制到臨時表 #t中,並增加一列id(自增列,即1,2,3,4,5,6...)select col1, col2, col3=dateadd(minute, ID*5, col3) from #Tdateadd(minute, ID*5, col3)--將時間增加id*5分鐘,即增加5,10,15,20...
--將原表數據復制到臨時表 #t中,並增加一列id(自增列,即0,1,2,3,4,5,6...)select col1, col2, col3=dateadd(minute, ID*5, col3) from #Tdateadd(minute, ID*5, col3)--將時間增加id*5分鐘,即增加0,5,10,15,20...
insert T select NULL,0,'2007-01-20 13:20:00.000'
union all select NULL,0,'2007-01-20 13:20:00.000'謝謝你了,你再幫我看看上面這個的意思好嗎
这个知道什么意思吧?就不解释了:)
insert T select NULL,0,'2007-01-20 13:20:00.000'
union all select NULL,0,'2007-01-20 13:20:00.000'
上面的意思是把数据:
NULL,0,'2007-01-20 13:20:00.000' 和
NULL,0,'2007-01-20 13:20:00.000'
插入表T中,跟下面语句一样的意思,只是写法不一样而已:)
insert into T (col1, col2, col3) values(NULL,0,'2007-01-20 13:20:00.000')
insert into T (col1, col2, col3) values(NULL,0,'2007-01-20 13:20:00.000')
http://community.csdn.net/Expert/topic/5317/5317671.xml?temp=.8086511