使用PL/SQL查找缺少的关系

问题描述

我有3个表，一个Users表，一个Preferences表和一个User_Preferences表.他们看起来像这样:

I have 3 tables, a Users table, a Preferences table, and a User_Preferences table. They look like this:

Users表格:

• 用户名:主键
• 用户名:用户名
• ...其他不重要的字段

• User_ID: Primary Key
• User_Name: Username
• ... other unimportant fields

Preferences表格:

• Pref_ID :主键
• 首选项名称:首选项名称
• ...其他不重要的字段

• Pref_ID: Primary Key
• Pref_Name: Preference Name
• ... other unimportant fields

User_Preferences表格:

• Pref_ID :首选项所针对的用户
• 用户名:正在设置首选项
• Pref_Val :首选项的值

• Pref_ID: The User the Preference is for
• User_ID: The Preference being set
• Pref_Val: The Value of the Preference

这些年来，已经添加了许多新的首选项，但是现在基础体系结构已经发生了一些变化.以前，如果User_Preferences表没有包含某个Preference的链接，那么没关系，但是现在，每个Preference都需要分配给每个User.

Over the years, many new Preferences have been added, but now the underlying architecture has change somewhat. Before, if the User_Preferences table didn't contain a link for a certain Preference, no matter, but now, every Preference needs to be assigned to every User.

因此，考虑到这三个表已填充，我可以运行哪些查询，该查询将为我提供缺少的User_Preferences列表?

So, given those three tables are populated, what query can I run that will give me a list of User_Preferences that are missing?

例如，如果有2个用户和2个权限，并且第一个用户同时拥有，但第二个用户拥有一个，它将为我提供该用户和该权限的一行

For example, if there are 2 users, and 2 permissions, and the first user has both but the second has one, it will give me a single row for that user and that permission

推荐答案

您可以使用cross join生成所有组合，然后使用left join过滤掉存在的组合:

You can do this using a cross join to generate all combinations and then filter out the ones that exist using a left join:

select u.*, p.*
from users u cross join
     preferences p left join
     user_preferences up
     on up.user_id = u.user_id and up.preference_id = p.preference_id
where up.user_id is null;

这篇关于使用PL/SQL查找缺少的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！