我有一个PostgreSQL数据库,有三个表:
user
:应用程序的用户列表。主键为user_id
course
:用户可以注册的课程目录。主键为course_id
enrollment
:用户注册课程的记录。主键为enrollment_id
表格之间的关系:
-
enrollment
表有两个不能为空的外键:enrollment.user_id
引用了user.user_id
- CCD_ 10参考CCD_
-
如果
user1
在course1
中注册,则enrollment
表中应该只有一条记录可用于将user1
联接到course1
。 -
如果
user1
是而不是在course1
中注册,则enrollment
表中不存在将user1
加入course1
的记录。
问题:
- 我找不到如何编写数据库视图,如果我有2个用户和3个课程,它会返回6行,如下所示:
user_id | course_id | enrollment_id
-------------------------------------
user1 | course1 | enrollment id of user1 for course1 or null
user1 | course2 | enrollment id of user1 for course2 or null
user1 | course3 | enrollment id of user1 for course3 or null
user2 | course1 | enrollment id of user2 for course1 or null
user2 | course2 | enrollment id of user2 for course2 or null
user2 | course3 | enrollment id of user2 for course3 or null
某些上下文:
- 我无法更改数据库的任何内容
- 我不关心表现
我的工作正在进行中:
我坦率地希望这能奏效:
select user.user_id, course.course_id, enrollment.enrollment_id
from user
full outer join enrollment on enrollment.user_id = user.user_id
full outer join course on course.course_id = enrollment.course_id
由于没有用户未注册课程的enrollment
记录,因此此查询不会返回我预期的结果。例如,结果看起来更像这样:
user_id | course_id | enrollment_id
-------------------------------------
user1 | null | null
user2 | course1 | enrollment id of user2 for course1
user2 | course2 | enrollment id of user2 for course2
这不是我所期望的,因为我希望该表返回未参加任何课程的user1
的3条记录,而不是仅返回course_id
和enrollment_id
为null
的一行。同样,user2
的第三个记录也不见了,因为他们没有加入course3
,这不是我所希望的。
我该如何解决这个问题?
交叉加入用户&课程,左加入注册:
select u.user_id, c.course_id, e.enrollment_id
from users u
cross join courses c
left join enrollment e
on e.user_id = u.user_id
and e.course_id = c.course_id
dbfiddle这里(Postgres 14(