如何查询前七天每天的某表字段数据总和

测试表一如下：

首先创建前七天的日期临时表：

 1 IF object_id('tempdb..#') IS NOT NULL DROP TABLE #; CREATE TABLE # ([date] datetime)
 2 DECLARE @d datetime,
 3 @i INT
 4 SET @d = GETDATE()
 5 SET @i = 1
 6 WHILE @I <= 7
 7 BEGIN
 8 INSERT # SELECT
 9 @d
10 SET @d = dateadd(dd ,- 1 ,@d)
11 SET @i =@i + 1
12 END;

然后是查询NumTest的想要的数据：

SELECT
a.Num,
CONVERT (CHAR(10), a.addTime, 120) AS addTime
FROM
NumTest AS a

然后是使用左联：

SELECT
SUM (isnull(bb.Num, 0)) AS SumNum,
CONVERT (CHAR(5), aa. DATE, 107) AS AddDate
FROM
(SELECT DATE FROM #) aa
LEFT JOIN (
SELECT
a.Num,
CONVERT (CHAR(10), a.addTime, 120) AS addTime
FROM
NumTest AS a
) bb ON CONVERT (CHAR(10), aa. DATE, 120) = CONVERT (CHAR(10), bb.addTime, 120)
GROUP BY
aa. DATE

将第一段代码和第三段代码合并，就可以成功的查询到当前日期前七天的Num总和了。