PostgreSQL：选择具有与另一个表中的所有条目相对应的条目的所有类型 [英] PostgreSQL: select all types that have an entry corresponding to all entries in another table

问题描述

我有两个桌子，生产者和滑雪板。生产者有他们的ID，每个滑雪板模型都知道其生产者的ID，每个模型也都有一个类型，这在滑雪板中并不唯一。我需要选择所有生产者生产的所有类型（无论型号如何）。我写了一个查询：

I have two tables, producers and skis. Producers have their ID, and each model of skis knows the ID of its producer, each model also has a type, which is not unique among skis. I need to select all types that are produced by all of the producers (regardless of models). I wrote a query:

select type 
from skis s
where not exists (
    select name from producers p
    except
    (
        select name 
        from producers p
        where (p.name=s.producer)
    )
);

仅当我有1个滑雪板和1个滑雪板时，它才起作用。这样做的好方法是什么？

It only works when I have 1 ski and one producer. What is a good way to do that?

为澄清起见：在生产者表中，名称列是其ID，在滑雪者表中，是生产者ID列是生产者。

EDIT for clarification: in the producer table, the column 'name' is their ID, and in the ski table the producers ID column is 'producer'.

推荐答案

计算每种类型的生产者数量并与生产者总数进行比较：

Count the number of producers per type and compare with the total number of producers:

select type
from skis
group by type
having count(distinct producer) = (select count(*) from producers);

这篇关于PostgreSQL：选择具有与另一个表中的所有条目相对应的条目的所有类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！