代码之家 › 专栏 › 技术社区 › Arya

用查询填写桥接表

postgresql sql

Arya · 技术社区 · 7 年前

我有四个表,这些表的主要目的是具有多对多关键字到消息的关系。每个关键字可以有多条消息,如果类别id匹配,则每条消息可以有多个关键字,它们可以关联在一起。

CREATE TABLE public.trigger_category
(
    id integer NOT NULL DEFAULT nextval('trigger_category_id_seq'::regclass),
    description text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT trigger_category_id PRIMARY KEY (id)
)
CREATE TABLE public.trigger_keyword
(
    id integer NOT NULL DEFAULT nextval('trigger_keyword_id_seq'::regclass),
    keyword text COLLATE pg_catalog."default" NOT NULL,
    category_id bigint NOT NULL,
    CONSTRAINT trigger_keyword_id PRIMARY KEY (id),
    CONSTRAINT trigger_keyword_category_id_fkey FOREIGN KEY (category_id)
        REFERENCES public.trigger_category (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION
)
CREATE TABLE public.trigger_message
(
    id integer NOT NULL DEFAULT nextval('trigger_message_id_seq'::regclass),
    message text COLLATE pg_catalog."default" NOT NULL,
    category_id bigint NOT NULL,
    CONSTRAINT trigger_message_id PRIMARY KEY (id),
    CONSTRAINT trigger_message_category_id_fkey FOREIGN KEY (category_id)
        REFERENCES public.trigger_category (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
CREATE TABLE public.trigger_keyword_trigger_message
(
    trigger_keyword_id bigint NOT NULL,
    trigger_message_id bigint NOT NULL,
    CONSTRAINT trigger_keyword_trigger_message_trigger_keyword_id_trigger_mess PRIMARY KEY (trigger_keyword_id, trigger_message_id),
    CONSTRAINT trigger_keyword_trigger_message_trigger_keyword_id_fkey FOREIGN KEY (trigger_keyword_id)
        REFERENCES public.trigger_keyword (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION,
    CONSTRAINT trigger_keyword_trigger_message_trigger_message_id_fkey FOREIGN KEY (trigger_message_id)
        REFERENCES public.trigger_message (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE NO ACTION
)

我手动在列表中插入关键字 trigger_keyword 表和我在中手动插入消息 trigger_message 表,如果它们是相关的,那么它们将得到相同的结果 category_id

然后,它将为桥接表创建所有适当的行 trigger_keyword_trigger_message ?

1 回复 | 直到 7 年前

GMB 7 年前

您可以使用 Oracle Merge Query .

USING 子句选择要插入的所有记录,并且 WHEN MATCHED

MERGE INTO trigger_keyword_trigger_message tktm
USING (
    SELECT tk.id tk_id, tm.id tm_id
    FROM
        trigger_keyword tk
        INNER JOIN trigger_message tm on tm.category_id = tk.category_id

) us
WHEN MATCHED THEN
    INSERT (tktm.trigger_keyword_id, tktm.trigger_message_id)
    VALUES (us.tk_id, us.tm_id)
;