整理多个技术点的sql综合应用：(开拓思路，SELECT子句内嵌子查询)

写的不好，欢迎拍砖！呵呵，也请高手指点！

  1 

 2  --设定起始日期为当前年的第一天
 3  DECLARE @dtDate DATETIME
 4  SET @dtDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
 5  
 6  --批量生成测试数据，日期规则递增的列、及随机的数值列
 7  SELECT TOP 1000
 8          IDENTITY( INT, 0, 1 ) AS OID,
 9          @dtDate AS dtBillDate,
10          CHECKSUM(NEWID()) % 100 AS dcQty
11  INTO    #tmp
12  FROM    sysobjects, syscolumns
13  
14  --按规则更新日期列（有没有更好的方式直接生成此递增的日期列？？期待高手！）
15  UPDATE  #tmp
16  SET     dtBillDate = DATEADD(DAY, OID, dtBillDate)
17  
18  --显示日期列、当前数值、累计数值
19  SELECT  a.OID,
20          a.dtBillDate,
21          a.dcQty,
22          ( SELECT    SUM(b.dcQty)
23            FROM      #tmp AS b
24            WHERE     b.dtBillDate <= a.dtBillDate
25          ) AS dcTotalQty
26  FROM    #tmp AS a
27  
28  DROP TABLE #tmp
29