加入表格,显示每周的所有记录

我希望有人能够帮助我.我有两张桌子,一张是学生姓名,另一张是每个学生每周完成的家庭作业,不是一个真实的例子.只有一名学生做过任何工作.我希望看到一张表格,显示所有学生和他们做了多少工作(即使每周都是空的.

CREATE TABLE #NAME (Name VARCHAR(20))

INSERT INTO #NAME VALUES
('John'),('Tom'),('Jack')

CREATE TABLE #TIME (Name VARCHAR(20), Week INT, Year INT, Total INT)

INSERT INTO #TIME VALUES
('John',1,2017,34),('John',2,2017,24),('John',3,2017,65),('John',4,2017,22),('John',5,2017,45)

我认为左外连接可以工作 - 但它只引用名称之间的连接而不是周

SELECT 
#Name.Name,
#Time.Week,
#Time.Year,
#Time.Total

FROM #NAME LEFT OUTER JOIN #Time ON #NAME.Name = #Time.Name

我尝试了外部申请 - 但基本上是同样的事情 -

SELECT 
#Name.Name,
A.Week,
A.Year,
A.Total

FROM #NAME OUTER APPLY (SELECT * FROM #TIme WHERE #Name.Name = #Time.Name) A 

以上两个查询的输出如下所示 - 与我想要获得的内容 - 每周重复一次,显示所有学生,无论他们是否有任何相关的值.

如果有人可以帮助我,我会非常感激.

你需要得到的所有组合Name,Week以及Year,然后做一个LEFT JOIN上#TIME以获得所需的结果:

WITH CteNameWeekYear(Name, [Week], [Year]) AS(
    SELECT
        n.*, t.*
    FROM(
        SELECT DISTINCT [Week], [Year]  FROM #TIME
    ) t
    CROSS JOIN #Name n
)
SELECT
    c.Name,
    c.[Week],
    c.[Year],
    t.Total
FROM CteNameWeekYear c
LEFT JOIN #TIME t
    ON c.Name = t.Name
    AND c.[Year] = t.[Year]
    AND c.[Week] = t.[Week]
ORDER BY c.[Year], c.[Week], c.Name;

ONLINE DEMO