查找具有多个特定类型关系的实体

Question

给定Postgres模式:

DROP TABLE IF EXISTS asset_category;
DROP TABLE IF EXISTS asset;
DROP TABLE IF EXISTS category;

CREATE TABLE asset (
"id" BIGINT NULL DEFAULT NULL,
"name" TEXT NULL DEFAULT NULL,
PRIMARY KEY ("id")
);
CREATE INDEX IF NOT EXISTS "IDX_id" ON asset (id);
CREATE TABLE category (
"id" BIGINT NULL DEFAULT NULL,
"type" TEXT NULL DEFAULT NULL,
"name" TEXT NULL DEFAULT NULL,
PRIMARY KEY ("id")
);
CREATE INDEX IF NOT EXISTS "IDX_id" ON category (id);
CREATE TABLE asset_category (
"asset_id" BIGINT NULL DEFAULT NULL,
"category_id" BIGINT NULL DEFAULT NULL,
CONSTRAINT "FK_asset_id" FOREIGN KEY ("asset_id") REFERENCES "asset" ("id") ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT "FK_category_id" FOREIGN KEY ("category_id") REFERENCES "category" ("id") ON UPDATE CASCADE ON DELETE SET NULL,
UNIQUE (asset_id, category_id)
);

和一些演示数据

INSERT INTO asset (id, "name") VALUES(1, 'Awesome Asset');
INSERT INTO asset (id, "name") VALUES(2, 'Great Asset');
INSERT INTO asset (id, "name") VALUES(3, 'Super Asset');
INSERT INTO asset (id, "name") VALUES(4, 'Best Asset');
INSERT INTO asset (id, "name") VALUES(5, 'Hilarious Asset');
INSERT INTO category (id, "name", "type") VALUES(1, 'Awesome Category', NULL);
INSERT INTO category (id, "name", "type") VALUES(2, 'Great Category', 'folder');
INSERT INTO category (id, "name", "type") VALUES(3, 'Super Category', 'folder');
INSERT INTO category (id, "name", "type") VALUES(4, 'Best Category', NULL);
INSERT INTO category (id, "name", "type") VALUES(5, 'Hilarious Category', NULL);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(1, 1);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(1, 2);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(2, 3);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(2, 4);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(3, 1);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(3, 2);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(3, 3);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(4, 5);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(5, 1);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(5, 2);
INSERT INTO asset_category ("asset_id", "category_id") VALUES(5, 3);

我如何创建一个SQL查询，返回所有的资产，有一个以上类别的类型folder相关联?

在这里我们可以看到，情况是这样的:

SELECT *
FROM asset a
LEFT JOIN asset_category ac ON a.id = ac.asset_id
LEFT JOIN category c ON c.id = ac.category_id AND c.type = 'folder'
WHERE c.id IS NOT NULL 

资产3和5大于1

我如何用查询来识别这些?

Answer 1

我想我有

SELECT a.id, a.name
FROM asset a
JOIN (
SELECT asset_id
FROM asset_category ac
JOIN category c ON ac.category_id = c.id
WHERE c.type = 'folder'
GROUP BY asset_id
HAVING COUNT(*) > 1
) sub ON a.id = sub.asset_id;