sql - 如何在另一个表中查找缺少属性 ID 的记录 ID

Question

PostgreSQL 数据库有两个表:user_properties 和 properties。 properties 表包含所有可能带有 id 的属性列表(字典)。 user_properties 表包含用户拥有的属性，引用属性表中的属性 id。

属性表:

----------------------
  prop_id | prop_name
----------------------
     1    | Email
----------------------
     2    | Phone number
----------------------
     3    | Something else 1
----------------------
     4    | Something else 2
----------------------

user_properties 表:

--------------------------------
  user_id | prop_id | prop_value
--------------------------------
    100   |    1    | asd@zxc.com
--------------------------------
    100   |    2    | 1234567
--------------------------------
    100   |    2    | 2345678
--------------------------------
    101   |    3    | *******
--------------------------------
    101   |    3    | +++++++
--------------------------------

我需要知道每个 user_id 缺少哪些属性。预期结果应如下所示:

-----------------------
  user_id | missing_prop_id
-----------------------
    100   |    3
-----------------------
    100   |    4
-----------------------
    101   |    1
-----------------------
    101   |    2
-----------------------
    101   |    4
-----------------------

Answer 1

您可以将 except 用作:

with properties(prop_id,prop_name) as
(
  values(1, 'Email'),(2, 'Phone number'),
        (3, 'Something else 1'),(4, 'Something else 2')  
), user_properties(  user_id, prop_id, prop_value) as
(
  values(100,1,'asd@zxc.com'),(100,2,'1234567'),(100,2,'2345678'),
        (101,3,'*******'),(101,3,'+++++++') 
), t2 as
(
select u.user_id, p.prop_id as missing_prop_id
  from user_properties u
 cross join properties p
 group by u.user_id, p.prop_id 
except 
select u.user_id, 
       p.prop_id
  from user_properties u
  right join properties p
    on u.prop_id = p.prop_id
 group by u.user_id, p.prop_id   
) 
select * from t2 order by user_id, missing_prop_id;

user_id      missing_prop_id
100          3
100          4
101          1
101          2
101          4

Demo