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

左联接返回重复行

postgresql sql

nomadoda · 技术社区 · 7 年前

我只是在学习SQL,我真的很难理解为什么我的左联接返回了重复的行。这是我使用的查询:

SELECT "id", "title"
FROM "posts"
LEFT JOIN "comments" "comment"
ON "comment"."post_id"="id" AND ("comment"."status" = 'hidden')

它返回 4行 ,但只应返回3。返回的两行包含重复的(相同的值)。我可以用 DISTINCT 前缀 "id" .

SELECT DISTINCT "id", "title"
FROM "posts"
LEFT JOIN "comments" "comment"
ON "comment"."post_id"="id" AND ("comment"."status" = 'hidden')

查询返回 3行我得到了期望的结果。但我仍然想知道,为什么在这个世界上,我会首先从第一个查询中得到一个重复的行?我正在尝试编写聚合查询,这似乎是我遇到的问题。

我正在使用PostgreSQL。

更具体地说: (由我的ORM创建)

移位DDL

CREATE TABLE shift (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    "gigId" uuid REFERENCES gig(id) ON DELETE CASCADE,
    "categoryId" uuid REFERENCES category(id),
    notes text,
    "createdAt" timestamp without time zone NOT NULL DEFAULT now(),
    "updatedAt" timestamp without time zone NOT NULL DEFAULT now(),
    "salaryFixed" numeric,
    "salaryHourly" numeric,
    "salaryCurrency" character varying(3) DEFAULT 'SEK'::character varying,
    "staffingMethod" character varying(255) NOT NULL DEFAULT 'auto'::character varying,
    "staffingIspublished" boolean NOT NULL DEFAULT false,
    "staffingActivateon" timestamp with time zone,
    "staffingTarget" integer NOT NULL DEFAULT 0
);

轮班员工DDL

CREATE TABLE "shiftEmployee" (
    "employeeId" uuid REFERENCES employee(id) ON DELETE CASCADE,
    "shiftId" uuid REFERENCES shift(id) ON DELETE CASCADE,
    status character varying(255) NOT NULL,
    "updatedAt" timestamp without time zone NOT NULL DEFAULT now(),
    "salaryFixed" numeric,
    "salaryHourly" numeric,
    "salaryCurrency" character varying(3) DEFAULT 'SEK'::character varying,
    CONSTRAINT "PK_6acfd2e8f947cee5a62ebff08a5" PRIMARY KEY ("employeeId", "shiftId")
);

查询

SELECT "id", "staffingTarget" FROM "shift" LEFT JOIN "shiftEmployee" "se" ON "se"."shiftId"="id" AND ("se"."status" = 'confirmed');

结果

id                                      staffingTarget
68bb0892-9bce-4d08-b40e-757cb0889e87    3
12d88ff7-9144-469f-8de5-3e316c4b3bbd    6
73c65656-e028-4f97-b855-43b00f953c7b    5
68bb0892-9bce-4d08-b40e-757cb0889e88    3
e3279b37-2ba5-4f1d-b896-70085f2ba345    4
e3279b37-2ba5-4f1d-b896-70085f2ba346    5
e3279b37-2ba5-4f1d-b896-70085f2ba346    5
789bd2fb-3915-4cda-a3d7-2186cf5bb01a    3

1 回复 | 直到 7 年前

a_horse_with_no_name 7 年前

如果一篇文章有多个隐藏的注释,那么您将看到该文章多次,因为一个连接为每个匹配返回一行,这就是连接的本质。外部连接的行为也没有不同。

如果您的目的是只列出带有隐藏注释的文章,最好使用exists查询:

SELECT p.id, p.title
FROM posts p
where exists (select *
              from comments c 
              where c.post_id = p.id 
                and c.status = 'hidden');