SQL Server 2008中使用外连接/左连接进行分组(group by)

Question

I have data in 1 table as

UserID CourseID TestID   Result
1         1       1        P
1         1       2        P
2         1       1        F
2         1       2        F

这里显示有两个用户（UserID=1和2）在课程ID为1的情况下参加了测试（TestID=1和2）。

现在这个课程ID总共有3个测试（例如：TestID=1,2,7）

CourseID TestID
1          1
1          2
1          7

这意味着没有用户参加了测试（测试ID = 7），现在我想按以下方式显示数据。

UserID CourseID TestID   Result
1         1       1        P
1         1       2        P
1         1       7        null
2         1       1        F
2         1       2        F
2         1       7        null

我一直在尝试使用左/右外连接和分组，但无法得到所需的结果。如何实现？

- Manish Gupta

---

它是 SQL Server 2008 R2。 - Manish Gupta

Answer 1

SQL Fiddle

MS SQL Server 2008架构设置:

create table Result
(
  UserID int,
  CourseID int,
  TestID int,
  Result char(1)
)
insert into Result values
(1,         1,       1,        'P'),
(1,         1,       2,        'P'),
(2,         1,       1,        'F'),
(2,         1,       2,        'F')

create table Course
(
  CourseID int,
  TestID int
)

insert into Course values
(1, 1),
(1, 2),
(1, 7)

查询 1:

select U.UserID,
       C.CourseID,
       C.TestID,
       R.Result
from (
     select distinct UserID
     from Result
     ) as U
  cross apply Course as  C
  left outer join Result as R
    on R.CourseID = C.CourseID and
       R.TestID = C.TestID and
       R.UserID = U.UserID

Results:

| USERID | COURSEID | TESTID | RESULT |
---------------------------------------
|      1 |        1 |      1 |      P |
|      1 |        1 |      2 |      P |
|      1 |        1 |      7 | (null) |
|      2 |        1 |      1 |      F |
|      2 |        1 |      2 |      F |
|      2 |        1 |      7 | (null) |

注意：如果您有一张名为Users的表，您可以替换派生表。

 (
 select distinct UserID
 from Result
 ) as U

请使用Users as U代替。

- Mikael Eriksson

---

似乎按预期工作，只是关于最后一行的一个问题 - R.UserID = C.TestID 不应该是 R.UserID = U.UserID 吗？ - Manish Gupta

@user1509581 应该是 U.UserID - Mikael Eriksson

Answer 2

SELECT uct.UserId, ct.CourseID, ct.TestID, uct.Result
FROM CourseTest ct
LEFT JOIN UserCourseTest uct ON uct.CourseID=ct.CourseID AND uct.TestID=ct.TestID

- Joel Coehoorn

---

我之前尝试过这个，但它没有显示正确的结果。在这种情况下，输出如下所示： 用户ID 课程ID 测试ID 结果 1 1 1 P 1 1 2 P 2 1 1 F 2 1 2 F null 1 7 null 类似于上面的东西。 - Manish Gupta

我看到问题了，需要与用户表连接，r "select distinct userid" 派生表。不过我要去睡觉了。 - Joel Coehoorn