sql - 如何为我的任务编写正确的 sql 请求

Question

我有表格:Users(id,name,...),Knowledge(id,name,...) 和 Courses(id ,名字....)。每个用户都有多个“知识”(用户和知识按 ManyToMany 类型链接)。例如用户“User1”有 2 个知识“1”和“2”。类(class)还按 ManyToMany 类型与知识链接。但在这种情况下，知识是必需的。例如类(class)“类(class)1”需要知识“1”和“2”，用户可以访问该类(class)。类(class)“类(class) 2”需要知识“1”和“3”，用户无权访问此类(class)。

我想写正确的sql命令，返回所有类(class)，可以获取用户。我使用 PostgresSql。对不起我糟糕的英语。

我试过这个 sql 命令来加入类(class)和知识:

select c.id as "Course id", k.requiredknowledge_id  from course as c inner join courses_required_knowledges as k on k.course_id = c.id;

加入用户和知识的类似命令。

select u.id as "User id", k.knowledge_id from user_info as u inner join users_knowledge as k on k.userInfo_id = u.id where u.id = 1;

但我不明白如何“组合”这个命令以获得结果

Answer 1

select c.id as "Course", array_agg(k.requiredknowledge_id) as "Requiried knoweldge" , array_agg(uk.knowledge_id) as "User Knowledge"
from course as c 
right join courses_required_knowledges as k on k.course_id = c.id 
right join users_knowledge as uk on uk.userInfo_id = 1
group by c.id 
having array_agg(k.requiredknowledge_id) <@ array_agg(uk.knowledge_id);

这个例子对我有用。