主页 / dba / 问题 / 189817
Accepted
Arya
Asked: 2017-11-01 19:06:40 +0800 CST

返回至少包含一条消息的匹配项

  • 772

我有两个表，该match表跟踪匹配项，该message_log表跟踪匹配项的对话。现在我通过以下查询获取所有匹配项

SELECT * FROM match order by id DESC

但我想修改它，以便它只返回至少包含一条消息message_log且至少一条消息own_account设置为 false 的匹配项。

我的比赛表

CREATE TABLE public.match
(
    id bigint NOT NULL DEFAULT nextval('match_id_seq'::regclass),
    match_id character varying(500) COLLATE pg_catalog."default",
    name character varying(30) COLLATE pg_catalog."default",
    birthdate timestamp with time zone,
    bio text COLLATE pg_catalog."default",
    my_id character varying(500) COLLATE pg_catalog."default",
    username character varying(500) COLLATE pg_catalog."default",
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    CONSTRAINT match_pkey PRIMARY KEY (id),
    CONSTRAINT match_match_id_key UNIQUE (match_id),
    CONSTRAINT matcch_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

我的消息日志表

CREATE TABLE public.message_log
(
    id bigint NOT NULL DEFAULT nextval('message_log_id_seq'::regclass),
    message text COLLATE pg_catalog."default",
    from_id text COLLATE pg_catalog."default",
    to_id text COLLATE pg_catalog."default",
    match_id text COLLATE pg_catalog."default",
    unix_timestamp bigint,
    own_account boolean,
    reply_batch boolean DEFAULT false,
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    match_pk bigint,
    CONSTRAINT message_log_pkey PRIMARY KEY (id),
    CONSTRAINT message_log_message_from_id_to_id_match_id_unix_timestamp_key UNIQUE (message, from_id, to_id, match_id, unix_timestamp),
    CONSTRAINT message_log_account_fk FOREIGN KEY (account_id)
        REFERENCES public.account (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT message_log_match_id_fk FOREIGN KEY (match_pk)
        REFERENCES public.match (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)
postgresql

1 个回答

  1. Best Answer

    您可以使用EXISTS子句高效而简洁地完成此操作。

    SELECT *
FROM match
WHERE EXISTS (
  SELECT 1
  FROM message_log
  WHERE match_pk = match.id
  AND own_account IS FALSE
)
ORDER BY id DESC;

    顺便说一句，在表中有match_id, 和真的很令人困惑。通常，约定意味着您在不同的表中引用其他内容。match_pkmessage_log_id

    此外，在 PostgreSQL 中，我们从不使用character varying(x)，除非有规范规定x您应该只使用text.

    • 1

相关问题