set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[StoredProcedure1]
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
declare @time datetime
set @time=DATEADD(hour, DATEDIFF(hour, 0, getdate()), 0) 
declare ShopAmount cursor for SELECT     SUM(dbo.DlsDownloadLog.MediaID) AS Expr1, dbo.Shop.ShopID
FROM         dbo.DlsDownloadLog INNER JOIN
dbo.EndUser ON dbo.DlsDownloadLog.EndUserID = dbo.EndUser.EndUserID INNER JOIN
dbo.Shop ON dbo.EndUser.ShopID = dbo.Shop.ShopID
WHERE     (dbo.DlsDownloadLog.DownloadDate > dateadd(hour,-1,@time)) AND (dbo.DlsDownloadLog.DownloadDate <= @time)
GROUP BY dbo.Shop.ShopID

declare @amount int, @shopid int
open ShopAmount
Fetch ShopAmount into @amount,@shopid
While (@@fetch_status=0)
Begin

insert into DlsShopDownloadAmount
(
ShopID,
Amount,
RecordTime
)
values
(
@shopid,
@amount,
@time
)

Fetch ShopAmount into @amount,@shopid
End
close ShopAmount
deallocate ShopAmount

RETURN

SELECT sum(sizeamount) as counts
,[ShopID]
,dateadd(hh, - datepart(hour,downloadtime) % 24 ,downloadtime)
FROM [abc].[dbo].[ShopDownloadAmount]
group by shopid, dateadd(hh, - datepart(hour,downloadtime) % 24 ,downloadtime)
order by dateadd(hh, - datepart(hour,downloadtime) % 24 ,downloadtime)

结果如下
36730    2    2009-12-15 00:00:00.000
72342    2    2009-12-16 00:00:00.000
76858    2    2009-12-17 00:00:00.000
34094    2    2009-12-18 00:00:00.000
58178    2    2009-12-19 00:00:00.000