declare @tmpStartDate datetimeselect @tmpStartDate ='2009-12-01'declare @tmpEndDate datetimeselect @tmpEndDate ='2009-12-08'declare @PriceSnapshotSummaryTemp table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)declare @Summary table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)insert into @Summary values(1,10,'20091201','2009-12-01 00:00:00')
insert into @Summary values(2,11,'20091201','2009-12-01 02:00:00')
insert into @Summary values(3,12,'20091201','2009-12-01 03:00:00')
insert into @Summary values(4,10,'20091202','2009-12-02 02:00:00')
insert into @Summary values(5,14,'20091203','2009-12-03 03:00:00')
insert into @Summary values(6,15,'20091205','2009-12-05 00:00:00')
insert into @Summary values(7,16,'20091205','2009-12-05 03:00:00')
insert into @Summary values(8,17,'20091206','2009-12-06 02:00:00')
insert into @Summary values(9,18,'20091206','2009-12-06 05:00:00')select * from @Summary我现在要插入@PriceSnapshotSummaryTemp这张表,效果如下:insert into @PriceSnapshotSummaryTemp values(3,12,'20091201','2009-12-01 03:00:00')
insert into @PriceSnapshotSummaryTemp values(4,10,'20091202','2009-12-02 02:00:00')
insert into @PriceSnapshotSummaryTemp values(5,14,'20091203','2009-12-03 03:00:00')
insert into @PriceSnapshotSummaryTemp values(7,16,'20091205','2009-12-05 03:00:00')
insert into @PriceSnapshotSummaryTemp values(8,17,'20091206','2009-12-06 05:00:00')
insert into @PriceSnapshotSummaryTemp values(8,17,'20091206','2009-12-06 05:00:00')
insert into @PriceSnapshotSummaryTemp values(8,17,'20091206','2009-12-06 05:00:00')select * from @PriceSnapshotSummaryTempstartDate~endDate这8天内如果有同样的asOfDate,并且比最大的asOfDate小,取出@Summary表里最大loadTime的记录
如果没有同样的asOfDate,并且比最大的asOfDate小,不取数据
如果没有同样的asOfDate,并且比最大的asOfDate大,取出@Summary表里最大的asOfDate最大loadTime的记录,如上例子:
当startDate为1号时
记录为@Summary的3,12,'20091201','2009-12-01 03:00:00'(这条loadTime最大)
startDate为2号时
记录为@Summary的4,10,'20091202','2009-12-02 02:00:00'
startDate为3号时
记录为@Summary的5,14,'20091203','2009-12-03 03:00:00'
startDate为4号时,没有4号记录,并且比最大的asOfDate(20091206)小,因此无记录
startDate为5号时
记录为@Summary的7,16,'20091205','2009-12-05 03:00:00'
startDate为6号时
记录为@Summary的8,17,'20091206','2009-12-06 05:00:00'
startDate为7号时,没有7号记录,但比最大的asOfDate(20091206)大,因此取最大的记录
为8,17,'20091206','2009-12-06 05:00:00'
startDate为8号时,没有8号记录,但比最大的asOfDate(20091206)大,因此取最大的记录
为8,17,'20091206','2009-12-06 05:00:00'万分感谢!
记录为@Summary的3,12,'20091201','2009-12-01 03:00:00'(这条loadTime最大) 你的意思是不是取与BEGINDATE相同的ASOFDATE中LOADTIME最大的记录?
不好意思,这个地方写的有点不清楚比如我的startDate日期到了4号,这个日期在summay表里没有这一天的,但比最大的asOfDate(12月6号)小,这一天的记录被忽略掉如果我的startDate日期到了8号,这个日期在summay表里也没有这一天的,但比最大的asOfDate(12月6号) 大,那么取出summary表里6号asOfDate最大loadTime的记录
对,如果有相同时间的取出同样asOfDate最大loadTime的那条记录
insert into @Summary values(9,18,'20091206','2009-12-06 05:00:00')为什么8的LOADTIME比9大
...不好意思,这个写错了insert into @Summary values(9,18,'20091206','2009-12-06 05:00:00')这个去掉吧,应该是最大loadTime的
declare @Summary table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)insert into @Summary values(1,10,'20091201','2009-12-01 00:00:00')
insert into @Summary values(2,11,'20091201','2009-12-01 02:00:00')
insert into @Summary values(3,12,'20091201','2009-12-01 03:00:00')
insert into @Summary values(4,10,'20091202','2009-12-02 02:00:00')
insert into @Summary values(5,14,'20091203','2009-12-03 03:00:00')
insert into @Summary values(6,15,'20091205','2009-12-05 00:00:00')
insert into @Summary values(7,16,'20091205','2009-12-05 03:00:00')
insert into @Summary values(8,17,'20091206','2009-12-06 02:00:00')
insert into @Summary values(9,18,'20091206','2009-12-06 05:00:00')select * into #1 from @Summarydeclare @PriceSnapshotSummaryTemp table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)declare @tmpStartDate datetime
select @tmpStartDate ='2009-12-01'
declare @tmpEndDate datetime
select @tmpEndDate ='2009-12-08'insert into @PriceSnapshotSummaryTemp
select * from #1 A where loadTime = (select max(loadTime) from #1 where asOfDate = A.asOfDate )
and A.loadTime between @tmpStartDate and @tmpEndDate order by A.snapshotId
select * from @PriceSnapshotSummaryTemp
(5 行受影响)
snapshotId scheduleId asOfDate loadTime
----------- ----------- ----------- -----------------------
3 12 20091201 2009-12-01 03:00:00.000
4 10 20091202 2009-12-02 02:00:00.000
5 14 20091203 2009-12-03 03:00:00.000
7 16 20091205 2009-12-05 03:00:00.000
9 18 20091206 2009-12-06 05:00:00.000(5 行受影响)
谢谢!前面是对的,但这个少了两条,7号与8号的记录需要补上1206的记录的,因为比最大的asOfDate大,谢谢!
FROM (SELECT DATEADD(day,number,@tmpStartDate) AS Date
FROM master.dbo.spt_values
WHERE Type = 'P' AND DATEADD(day,number,@tmpStartDate) <= @tmpEndDate) AS A
LEFT JOIN (
SELECT * FROM @Summary AS A
WHERE loadTime = (SELECT MAX(loadTime) FROM @Summary
WHERE asOfDate = A.asOfDate)
) AS B
ON A.Date = B.asOfDate
OR CASE WHEN A.Date > (SELECT MAX(asOfDate) FROM @Summary)
THEN B.asOfDate ELSE NULL END = (SELECT MAX(asOfDate) FROM @Summary)
WHERE B.asOfDate IS NOT NULL
ORDER BY B.asOFDate
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)declare @Summary table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)insert into @Summary values(1,10,'20091201','2009-12-01 00:00:00')
insert into @Summary values(2,11,'20091201','2009-12-01 02:00:00')
insert into @Summary values(3,12,'20091201','2009-12-01 03:00:00')
insert into @Summary values(4,10,'20091202','2009-12-02 02:00:00')
insert into @Summary values(5,14,'20091203','2009-12-03 03:00:00')
insert into @Summary values(6,15,'20091205','2009-12-05 00:00:00')
insert into @Summary values(7,16,'20091205','2009-12-05 03:00:00')
insert into @Summary values(8,17,'20091206','2009-12-06 02:00:00')
;WITH MU1 AS(
select DISTINCT * from @Summary T1
WHERE NOT EXISTS(SELECT 1 FROM @SUMMARY T2 WHERE T2.ASOFDATE=T1.ASOFDATE AND T2.LOADTIME>T1.LOADTIME))
,MU2 AS (
SELECT CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,@tmpStartDate),112) 'ASOFDATE' FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(DAY,NUMBER,@tmpStartDate)<=@tmpEndDate
)
INSERT INTO @PriceSnapshotSummaryTemp
SELECT
CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.snapshotId ELSE MU1.snapshotId END
,CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.scheduleId ELSE MU1.scheduleId END
,CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.asOfDate ELSE MU1.asOfDate END
,CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.loadTime ELSE MU1.loadTime END
FROM MU2
LEFT JOIN MU1 ON MU2.ASOFDATE=MU1.ASOFDATE
INNER JOIN (
SELECT TOP 1 * FROM MU1 ORDER BY ASOFDATE DESC
) MU3 ON 1=1
ORDER BY
CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.snapshotId ELSE MU1.snapshotId END ASCSELECT * FROM @PriceSnapshotSummaryTemp/*
snapshotId scheduleId asOfDate loadTime
----------- ----------- ----------- -----------------------
3 12 20091201 2009-12-01 03:00:00.000
4 10 20091202 2009-12-02 02:00:00.000
5 14 20091203 2009-12-03 03:00:00.000
7 16 20091205 2009-12-05 03:00:00.000
8 17 20091206 2009-12-06 02:00:00.000
8 17 20091206 2009-12-06 02:00:00.000
8 17 20091206 2009-12-06 02:00:00.000
8 17 20091206 2009-12-06 02:00:00.000
*/
Type = 'P' ?我这个地方报错了,Msg 207, Level 16, State 1, Line 51
Invalid column name 'Type'.
(
snapshotId int,
scheduleId int,
asOfDate datetime,
loadTime datetime
)insert into @Summary values(1,10,'20091201','2009-12-01 00:00:00')
insert into @Summary values(2,11,'20091201','2009-12-01 02:00:00')
insert into @Summary values(3,12,'20091201','2009-12-01 03:00:00')
insert into @Summary values(4,10,'20091202','2009-12-02 02:00:00')
insert into @Summary values(5,14,'20091203','2009-12-03 03:00:00')
insert into @Summary values(6,15,'20091205','2009-12-05 00:00:00')
insert into @Summary values(7,16,'20091205','2009-12-05 03:00:00')
insert into @Summary values(8,17,'20091206','2009-12-06 02:00:00')
insert into @Summary values(9,18,'20091206','2009-12-06 05:00:00')SELECT B.*
FROM (SELECT DATEADD(day,number,@tmpStartDate) AS Date
FROM master.dbo.spt_values
WHERE Type = 'P' AND DATEADD(day,number,@tmpStartDate) <= @tmpEndDate) AS A
LEFT JOIN (
SELECT * FROM @Summary AS A
WHERE loadTime = (SELECT MAX(loadTime) FROM @Summary
WHERE asOfDate = A.asOfDate)
) AS B
ON A.Date = B.asOfDate
OR CASE WHEN A.Date > (SELECT MAX(asOfDate) FROM @Summary)
THEN B.asOfDate ELSE NULL END = (SELECT MAX(asOfDate) FROM @Summary)
WHERE B.asOfDate IS NOT NULL
ORDER BY B.asOFDate/*
snapshotId scheduleId asOfDate loadTime
----------- ----------- ----------------------- -----------------------
3 12 2009-12-01 00:00:00.000 2009-12-01 03:00:00.000
4 10 2009-12-02 00:00:00.000 2009-12-02 02:00:00.000
5 14 2009-12-03 00:00:00.000 2009-12-03 03:00:00.000
7 16 2009-12-05 00:00:00.000 2009-12-05 03:00:00.000
9 18 2009-12-06 00:00:00.000 2009-12-06 05:00:00.000
9 18 2009-12-06 00:00:00.000 2009-12-06 05:00:00.000
9 18 2009-12-06 00:00:00.000 2009-12-06 05:00:00.000(7 行受影响)
*/
好像LS已经写出答案了,LZ自己都试试吧,看哪个快就用哪个
declare @tmpStartDate datetimeselect @tmpStartDate ='2009-12-01'declare @tmpEndDate datetimeselect @tmpEndDate ='2009-12-08'declare @PriceSnapshotSummaryTemp table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)declare @Summary table
(
snapshotId int,
scheduleId int,
asOfDate int,
loadTime datetime
)insert into @Summary values(1,10,'20091201','2009-12-01 00:00:00')
insert into @Summary values(2,11,'20091201','2009-12-01 02:00:00')
insert into @Summary values(3,12,'20091201','2009-12-01 03:00:00')
insert into @Summary values(4,10,'20091202','2009-12-02 02:00:00')
insert into @Summary values(5,14,'20091203','2009-12-03 03:00:00')
insert into @Summary values(6,15,'20091205','2009-12-05 00:00:00')
insert into @Summary values(7,16,'20091205','2009-12-05 03:00:00')
insert into @Summary values(8,17,'20091206','2009-12-06 02:00:00')
;WITH MU1 AS(
select DISTINCT * from @Summary T1
WHERE NOT EXISTS(SELECT 1 FROM @SUMMARY T2 WHERE T2.ASOFDATE=T1.ASOFDATE AND T2.LOADTIME>T1.LOADTIME))
,MU2 AS (
SELECT CONVERT(VARCHAR(10),DATEADD(DAY,NUMBER,@tmpStartDate),112) 'ASOFDATE' FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(DAY,NUMBER,@tmpStartDate)<=@tmpEndDate
)
INSERT INTO @PriceSnapshotSummaryTemp
SELECT
CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.snapshotId ELSE MU1.snapshotId END
,CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.scheduleId ELSE MU1.scheduleId END
,CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.asOfDate ELSE MU1.asOfDate END
,CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.loadTime ELSE MU1.loadTime END
FROM MU2
LEFT JOIN MU1 ON MU2.ASOFDATE=MU1.ASOFDATE
INNER JOIN (
SELECT TOP 1 * FROM MU1 ORDER BY ASOFDATE DESC
) MU3 ON 1=1
WHERE (MU1.ASOFDATE IS NULL AND MU2.ASOFDATE<MU3.ASOFDATE) OR MU1.ASOFDATE IS NOT NULL
ORDER BY
CASE WHEN MU1.ASOFDATE IS NULL THEN MU3.snapshotId ELSE MU1.snapshotId END ASC
SELECT * FROM @PriceSnapshotSummaryTemp/*
snapshotId scheduleId asOfDate loadTime
----------- ----------- ----------- -----------------------
3 12 20091201 2009-12-01 03:00:00.000
4 10 20091202 2009-12-02 02:00:00.000
5 14 20091203 2009-12-03 03:00:00.000
7 16 20091205 2009-12-05 03:00:00.000
8 17 20091206 2009-12-06 02:00:00.000
8 17 20091206 2009-12-06 02:00:00.000
*/
不行啊,我们好像用不了这个MASTER..SPT_VALUESMsg 208, Level 16, State 1, Line 39
Invalid object name 'MASTER..SPT_VALUES'.
(
snapshotId int,
scheduleId int,
asOfDate datetime,
loadTime datetime
)insert into @Summary values(1,10,'20091201','2009-12-01 00:00:00')
insert into @Summary values(2,11,'20091201','2009-12-01 02:00:00')
insert into @Summary values(3,12,'20091201','2009-12-01 03:00:00')
insert into @Summary values(4,10,'20091202','2009-12-02 02:00:00')
insert into @Summary values(5,14,'20091203','2009-12-03 03:00:00')
insert into @Summary values(6,15,'20091205','2009-12-05 00:00:00')
insert into @Summary values(7,16,'20091205','2009-12-05 03:00:00')
insert into @Summary values(8,17,'20091206','2009-12-06 02:00:00')
insert into @Summary values(9,18,'20091206','2009-12-06 05:00:00')SELECT TOP 1000 number = IDENTITY(int,1,1) INTO #tmp FROM sysobjects,syscolumnsSELECT B.*
FROM (SELECT DATEADD(day,number,@tmpStartDate) AS Date
FROM #tmp
WHERE DATEADD(day,number,@tmpStartDate) <= @tmpEndDate) AS A
LEFT JOIN (
SELECT * FROM @Summary AS A
WHERE loadTime = (SELECT MAX(loadTime) FROM @Summary
WHERE asOfDate = A.asOfDate)
) AS B
ON A.Date = B.asOfDate
OR CASE WHEN A.Date > (SELECT MAX(asOfDate) FROM @Summary)
THEN B.asOfDate ELSE NULL END = (SELECT MAX(asOfDate) FROM @Summary)
WHERE B.asOfDate IS NOT NULL
ORDER BY B.asOFDate;
DROP TABLE #tmp/*
snapshotId scheduleId asOfDate loadTime
----------- ----------- ----------------------- -----------------------
4 10 2009-12-02 00:00:00.000 2009-12-02 02:00:00.000
5 14 2009-12-03 00:00:00.000 2009-12-03 03:00:00.000
7 16 2009-12-05 00:00:00.000 2009-12-05 03:00:00.000
9 18 2009-12-06 00:00:00.000 2009-12-06 05:00:00.000
9 18 2009-12-06 00:00:00.000 2009-12-06 05:00:00.000
9 18 2009-12-06 00:00:00.000 2009-12-06 05:00:00.000(6 行受影响)
*/
Invalid column name 'Type'.
还有 MASTER..SPT_VALUES 也没有可能是权限问题
SELECT TOP 1000 number = IDENTITY(int,1,1) INTO #tmp FROM sysobjects,syscolumns改为SELECT TOP 1000 number = IDENTITY(int,0,1) INTO #tmp FROM sysobjects,syscolumns