sql - SQL在另一个表中不存在，但在另一个表中count大于3

Question

我有以下表格，其中包含photo每个个人资料的个人资料和照片列表（在表格中）。我还有一个service表和一个表，我想要一个查询，该查询将返回不属于使用的服务的配置文件 ID，并且表used中还有超过 3 张照片photo

profile桌子_

CREATE TABLE public.profile
(
    id integer NOT NULL DEFAULT nextval('profile_id_seq'::regclass),
    name text COLLATE pg_catalog."default" NOT NULL,
    birthday timestamp with time zone NOT NULL,
    CONSTRAINT profile_id PRIMARY KEY (id)
)

photo桌子_

CREATE TABLE public.photo
(
    id integer NOT NULL DEFAULT nextval('photo_id_seq'::regclass),
    image bytea NOT NULL,
    image_id text COLLATE pg_catalog."default" NOT NULL,
    order_count smallint NOT NULL,
    profile_id bigint NOT NULL,
    CONSTRAINT photo_id PRIMARY KEY (id),
    CONSTRAINT photo_profile_id_fkey FOREIGN KEY (profile_id)
        REFERENCES public.profile (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

service桌子_

CREATE TABLE public.service
(
    id integer NOT NULL DEFAULT nextval('service_id_seq'::regclass),
    name text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT service_id PRIMARY KEY (id)
)

used桌子_

CREATE TABLE public.used
(
    id integer NOT NULL DEFAULT nextval('used_id_seq'::regclass),
    service_id bigint NOT NULL,
    profile_id bigint NOT NULL,
    insert_timestamp timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT used_id PRIMARY KEY (id),
    CONSTRAINT used_profile_id_fkey FOREIGN KEY (profile_id)
        REFERENCES public.profile (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT used_service_id_fkey FOREIGN KEY (service_id)
        REFERENCES public.service (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

标签： sqlpostgresql

Answer 1

使用存在和不存在

     select p.* from profile p
     where exists ( select 1 from photo ph where ph.profile_id =p.id
                                           having count (distinct image_id )=3
                   )
    and not exists ( select 1 from used u where u.profile_id =p.id)