SQL根据第二个表中的值列表从第一个表中选择

Question

我有这样的表“人”

  id  |  firstName  |  lastName  |  ...
-----------------------------------------
  1   | Bill        | Gates      |
  2   | Steve       | Jobs       |
  3   | Linus       | Torvalds   |

和二表“德”是这样的

  id  | person_id | v_key    | v_value
---------------------------------------------
  1   | 1         | OS       | Windows Mobile
  2   | 1         | Company  | Microsoft
  3   | 2         | OS       | iOS
  4   | 2         | Company  | Apple

我可以为所有这样的人计算美德

select a.firstName, a.lastName, count(b.v_key)
  from person a
  left join virtue b on b.person_id = a.id
  group by a.firstName, a.lastName

现在我需要从第一个表中选择所有给出了“OS”=“iOS”、“Company”=“Apple”等美德列表的人。

Answer 1

如果您需要获取拥有您所提供的所有美德的人，那么您可以使用group by 并根据count 进行过滤:

with cte_given_list as (
    select *
    from (values ('OS', 'iOS'), ('Company', 'Apple')) as c(v_key, v_value)
)
select
    p.firstName, p.lastName
from cte_given_list as c
    inner join virtue as v on v.v_key = c.v_key and v.v_value = c.v_value
    inner join person as p on p.id = v.person_id
group by p.id
having count(*) = (select count(*) from cte_given_list as t)

请注意，这里我按 ID 分组但选择名称。如果 id 是 person 表中的主键，则在 PostgreSQL 中是可能的。

sql fiddle demo