HELP!!!!
DECLARE @BeginTime DATETIME
DECLARE @EndTime datetime
SET @BeginTime = '2006-1-1 0:0:0'
SET @EndTime = '2006-2-1 0:0:0'set nocount on--定义统计的月份差值
declare @variable int
set @variable = datediff(month,@BeginTime,@EndTime)--定义变化从统计起始到结束的时间
declare @Begin datetime
declare @End datetime
set @Begin = convert(datetime,(left(convert(varchar,@BeginTime,2),6)+'01'))
set @End = convert(datetime,(left(convert(varchar,@EndTime,2),6)+'01'))
declare @T table(任务编码 char(20),编码 int)------------------------------------------------------------------------------0
if (select @variable) >= 0beginselect 任务编码,编码 = identity(int)
into #temp0
from Ttaskview
where 生成任务时刻>= @Begin and 生成任务时刻<=(dateadd(month,1,@begin))insert into @T
select * from #temp0
drop table #temp0end------------------------------------------------------------------------------1
if (select @variable) >= 1beginselect 任务编码,编码 = identity(int)
into #temp1
from Ttaskview
where 生成任务时刻>=(dateadd(month,1,@begin)) and 生成任务时刻<=(dateadd(month,2,@begin))insert into @T
select * from #temp1
drop table #temp1end------------------------------------------------------------------------------2
if (select @variable) >= 2beginselect 任务编码,编码 = identity(int)
into #temp2
from Ttaskview
where 生成任务时刻>= (dateadd(month,2,@begin))and 生成任务时刻<=(dateadd(month,3,@begin))insert into @T
select * from #temp2
drop table #temp2end------------------------------------------------------------------------------3
if (select @variable) >= 3beginselect 任务编码,编码 = identity(int)
into #temp3
from Ttaskview
where 生成任务时刻>= (dateadd(month,3,@begin))and 生成任务时刻<=(dateadd(month,4,@begin))insert into @T
select * from #temp3
drop table #temp3endselect * from @T请求改进,优化这个长长的垃圾语句~~~~
我自己写的,因为只能被动的计算差值为若干月的表!!!
而且十分的慢,呵呵!!!
DECLARE @BeginTime DATETIME
DECLARE @EndTime datetime
SET @BeginTime = '2006-1-1 0:0:0'
SET @EndTime = '2006-2-1 0:0:0'set nocount on--定义统计的月份差值
declare @variable int
set @variable = datediff(month,@BeginTime,@EndTime)--定义变化从统计起始到结束的时间
declare @Begin datetime
declare @End datetime
set @Begin = convert(datetime,(left(convert(varchar,@BeginTime,2),6)+'01'))
set @End = convert(datetime,(left(convert(varchar,@EndTime,2),6)+'01'))
declare @T table(任务编码 char(20),编码 int)------------------------------------------------------------------------------0
if (select @variable) >= 0beginselect 任务编码,编码 = identity(int)
into #temp0
from Ttaskview
where 生成任务时刻>= @Begin and 生成任务时刻<=(dateadd(month,1,@begin))insert into @T
select * from #temp0
drop table #temp0end------------------------------------------------------------------------------1
if (select @variable) >= 1beginselect 任务编码,编码 = identity(int)
into #temp1
from Ttaskview
where 生成任务时刻>=(dateadd(month,1,@begin)) and 生成任务时刻<=(dateadd(month,2,@begin))insert into @T
select * from #temp1
drop table #temp1end------------------------------------------------------------------------------2
if (select @variable) >= 2beginselect 任务编码,编码 = identity(int)
into #temp2
from Ttaskview
where 生成任务时刻>= (dateadd(month,2,@begin))and 生成任务时刻<=(dateadd(month,3,@begin))insert into @T
select * from #temp2
drop table #temp2end------------------------------------------------------------------------------3
if (select @variable) >= 3beginselect 任务编码,编码 = identity(int)
into #temp3
from Ttaskview
where 生成任务时刻>= (dateadd(month,3,@begin))and 生成任务时刻<=(dateadd(month,4,@begin))insert into @T
select * from #temp3
drop table #temp3endselect * from @T请求改进,优化这个长长的垃圾语句~~~~
我自己写的,因为只能被动的计算差值为若干月的表!!!
而且十分的慢,呵呵!!!
解决方案 »
- 请大家推荐一本MS SQL2005教材
- 50分 解决一个SQL查询问题
- 建立维护计划时出现错误:无法将 NULL 值插入列 'owner_sid',表 'msdb.dbo.sysjobs';该列不允许空值。INSERT 失败。
- 请问如何将两个结构相似的表中的数据汇总?(急!)
- 简单的sql语句!
- 系统数据表中哪张表记录了用户数据库的信息---求大神指导
- 关于自段自动计算的问题,立即给分。
- 求SQL语句!困惑了好久了,谢谢了!
- SQL Server视图可以加密吗?
- 请问这样的触发器该如何写呢?
- 一个关于排序筛选的问题,在线急等!!!
- HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!HELP!!!
DECLARE @ENDTIME DATETIME
DECLARE @I INT
SET @BEGINTIME = '2006-1-1 0:0:0'
SET @ENDTIME = '2006-2-1 0:0:0'SET NOCOUNT ON--定义统计的月份差值
DECLARE @VARIABLE INT
SET @VARIABLE = DATEDIFF(MONTH,@BEGINTIME,@ENDTIME)--定义变化从统计起始到结束的时间
DECLARE @BEGIN DATETIME
DECLARE @END DATETIME
SET @BEGIN = CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR,@BEGINTIME,2),6)+'01'))
SET @END = CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR,@ENDTIME,2),6)+'01'))
DECLARE @T TABLE(任务编码 CHAR(20),编码 INT)SET @I=0WHILE @I<=3
BEGIN
IF (SELECT @VARIABLE) >= @I
BEGIN
SELECT 任务编码,编码 = IDENTITY(INT)
INTO #TEMP0
FROM TTASKVIEW
WHERE 生成任务时刻>=(DATEADD(MONTH,@I,@BEGIN)) AND 生成任务时刻>= @BEGIN AND 生成任务时刻<=(DATEADD(MONTH,@I+1,@BEGIN))
INSERT INTO @T
SELECT * FROM #TEMP0
DROP TABLE #TEMP0
END
SET @I=@I+1
ENDSELECT * FROM @T
没有达到我的要求!!!
我没有说明:我要求如果时间差值为N个月的话,@T表里面的自动增加列就有N个。每个月单独自增!下个月开始第一个为1!!!呵呵!!!
DECLARE @BEGINTIME DATETIME
DECLARE @ENDTIME DATETIME
DECLARE @I INT
SET @BEGINTIME = '2006-1-1 0:0:0'
SET @ENDTIME = '2006-2-1 0:0:0'SET NOCOUNT ON--定义统计的月份差值
DECLARE @VARIABLE INT
SET @VARIABLE = DATEDIFF(MONTH,@BEGINTIME,@ENDTIME)--定义变化从统计起始到结束的时间
DECLARE @BEGIN DATETIME
DECLARE @END DATETIME
SET @BEGIN = CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR,@BEGINTIME,2),6)+'01'))
SET @END = CONVERT(DATETIME,(LEFT(CONVERT(VARCHAR,@ENDTIME,2),6)+'01'))
DECLARE @T TABLE(任务编码 CHAR(20),编码 INT)SET @I=0WHILE @I<=@VARIABLE
BEGIN
IF (SELECT @VARIABLE) >= @I
BEGIN
SELECT 任务编码,编码 = IDENTITY(INT)
INTO #TEMP0
FROM TTASKVIEW
WHERE 生成任务时刻>=(DATEADD(MONTH,@I,@BEGIN)) AND 生成任务时刻>= @BEGIN AND 生成任务时刻<=(DATEADD(MONTH,@I+1,@BEGIN))
INSERT INTO @T
SELECT * FROM #TEMP0
DROP TABLE #TEMP0
END
SET @I=@I+1
ENDSELECT * FROM @T这样就可以了!!!谢谢!!!