和连接两个表

Question

CREATE TABLE Daily 
    ([DATE] datetime, [sales] int) 
; 

INSERT INTO Daily 
    ([DATE], [sales]) 
VALUES 
    ('2012-01-01 00:00:00', 1), 
    ('2012-01-02 00:00:00', 2), 
    ('2012-01-03 00:00:00', 3), 
    ('2012-01-04 00:00:00', 4), 
    ('2012-01-05 00:00:00', 5), 
    ('2012-01-06 00:00:00', 6), 
    ('2012-01-06 00:00:00', 5), 
    ('2012-01-07 00:00:00', 7), 
    ('2012-01-08 00:00:00', 8), 
    ('2012-01-09 00:00:00', 9), 
    ('2012-01-10 00:00:00', 10), 
    ('2012-01-11 00:00:00', 11), 
    ('2012-01-12 00:00:00', 12), 
    ('2012-01-13 00:00:00', 13), 
    ('2012-01-14 00:00:00', 14), 
    ('2012-01-15 00:00:00', 15), 
    ('2012-01-16 00:00:00', 16) 
; 

CREATE TABLE Weekly 
    ([Weekly] datetime) 
; 

INSERT INTO Weekly 
    ([Weekly]) 
VALUES 
    ('2012-01-07 00:00:00'), 
    ('2012-01-14 00:00:00'), 
    ('2012-01-21 00:00:00') 
; 

之前，我想最后的输出

Sales  
1/7/2012  33 
1/14/2012  77 

任何帮助，将不胜感激。在此先感谢

Answer 1

我会强烈reccommend反对在表中存储这一点，如果你的任何日常数据的改变你的每周数据将需要改变，否则将是错误的，而不是创建一个视图如下：

CREATE VIEW Weekly 
AS 
    SELECT WeekEnd = DATEADD(WEEK, DATEDIFF(WEEK, 0, [DATE]) + 1, -2), 
      Sales = SUM(Sales) 
    FROM Daily 
    GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, [DATE]) + 1, -2); 

您可以像创建表格一样使用它，但这将始终与日常数据同步。如果您想更改星期的开始/结束日期（即星期一至星期日），则可以更改DATEADD函数中的-2以更改此项。

Example on SQL Fiddle

（基于[]各地的列名，我猜测这是SQL-Server中。）