同表一对多关系查询：如何获取各级分类数据？

现有表结构 #

CREATE TABLE IF NOT EXISTS public.djangoadmin_animal
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    slug character varying(100) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,
    date_create timestamp with time zone NOT NULL,
    date_update timestamp with time zone NOT NULL,
    category_id bigint NOT NULL,
    CONSTRAINT djangoadmin_animal_pkey PRIMARY KEY (id),
    CONSTRAINT djangoadmin_animal_category_id_3d95d2d9_fk_djangoadm FOREIGN KEY (category_id)
        REFERENCES public.djangoadmin_category (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

CREATE TABLE IF NOT EXISTS public.djangoadmin_category
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(100) COLLATE pg_catalog."default" NOT NULL,
    slug character varying(100) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default" NOT NULL,
    images character varying(100) COLLATE pg_catalog."default" NOT NULL,
    date_create timestamp with time zone NOT NULL,
    date_update timestamp with time zone NOT NULL,
    category_id bigint,
    CONSTRAINT djangoadmin_category_pkey PRIMARY KEY (id),
    CONSTRAINT djangoadmin_category_category_id_9b327c27_fk_djangoadm FOREIGN KEY (category_id)
        REFERENCES public.djangoadmin_category (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

分类表示例数据 #

11  "zoogdieren"    "zoogdieren"    "hoi"   "photos/categories/1_eDJtmdP.jpg"   "2023-01-27 18:25:18.624272+01" "2023-01-27 18:25:18.624272+01" 
12  "amfibieen" "amfibieen" "kujhkjh"   "photos/categories/1_KJDTBPc.jpg"   "2023-01-27 18:25:38.444066+01" "2023-01-27 18:25:38.444066+01" 
13  "vogels"    "vogels"    "kljhkjh"   "photos/categories/1_FGkA44b.jpg"   "2023-01-27 18:26:00.390812+01" "2023-01-27 18:26:00.390812+01" 
16  "roofvogels"    "roofvogels"    "kljhkljjl" "photos/categories/1_pA0TNrX.jpg"   "2023-01-27 18:29:16.101478+01" "2023-01-27 18:29:16.102479+01" 13
17  "kikkers"   "kikkers"   "kjhkjh"    "photos/categories/1_zk2WQLP.jpg"   "2023-01-27 18:29:44.073516+01" "2023-01-27 18:29:44.073516+01" 12
21  "reptielen" "reptielen" "reptielen" "photos/categories/1_EoVggfL.jpg"   "2023-01-27 18:55:04.565339+01" "2023-01-27 18:55:04.565339+01" 
22  "slangen"   "slangen"   "slangen"   "photos/categories/1_w4pzls7.jpg"   "2023-01-27 18:55:23.181336+01" "2023-01-27 18:55:23.181336+01" 21
23  "schildpadden"  "schildpadden"  "schildpadden"  "photos/categories/1_RkKQ5md.jpg"   "2023-01-27 18:55:51.724641+01" "2023-01-27 18:55:51.724641+01" 
24  "honden"    "ohhhh" "hhhh"  "photos/categories/1_iUcB8K5.jpg"   "2023-01-27 19:24:35.589541+01" "2023-01-27 19:24:35.590538+01" 11
25  "katten"    "kjhkjh"    "kjhkjh"    "photos/categories/1_5LxINWC.jpg"   "2023-01-27 19:24:48.07098+01"  "2023-01-27 19:24:48.07098+01"  11
26  "olifanten" "olifanten" "kjhkjhkjh" "photos/categories/1_kmRFovt.jpg"   "2023-01-27 19:25:05.648655+01" "2023-01-27 19:25:05.648655+01" 11
27  "krokodillen"   "krokodillen"   "l;l;'ll;;'l"   "photos/categories/1_YuCHCct.jpg"   "2023-01-27 19:27:18.06109+01"  "2023-01-27 19:27:18.06109+01"  12

分类层级说明 #

• 主分类：zoogdieren（哺乳动物）、amfibieen（两栖动物）、vogels（鸟类）
• 子分类示例：honden（狗）、katten（猫）属于主分类zoogdieren（哺乳动物）

需求说明 #

需要实现两类查询：

1. 获取所有主分类（无父分类的顶级分类）
2. 根据指定主分类，获取其下所有子分类

尝试的SQL（未得到预期结果） #

SELECT
    A.name, A.category_id, B.name, B.category_id
FROM 
    djangoadmin_category A, djangoadmin_category B
WHERE 
    A.category_id = B.category_id
    AND A.name = B.name
ORDER BY 
    A."name"

解决方案 #

1. 查询所有主分类 #

主分类的核心特征是category_id为NULL，直接筛选即可：

SELECT id, name, slug, description
FROM djangoadmin_category
WHERE category_id IS NULL
ORDER BY name;

该查询会返回所有顶级主分类，包括你提到的zoogdieren、amfibieen、vogels等。

2. 查询指定主分类下的所有子分类 #

按主分类名称查询

通过自连接关联父子分类，先定位主分类，再筛选其下属子分类：

SELECT child.id, child.name, child.slug
FROM djangoadmin_category child
JOIN djangoadmin_category parent ON child.category_id = parent.id
WHERE parent.name = 'zoogdieren'
ORDER BY child.name;

按主分类ID查询（更高效）

如果已知主分类的ID（比如zoogdieren的ID是11），可以直接简化查询：

SELECT id, name, slug, description
FROM djangoadmin_category
WHERE category_id = 11
ORDER BY name;

原SQL问题分析 #

你写的SQL条件A.category_id = B.category_id AND A.name = B.name逻辑错误，这只会筛选出分类自身与自身匹配的重复记录，无法建立父子分类的关联关系。正确的自连接逻辑应该是子分类的category_id等于父分类的id。

内容的提问来源于stack exchange，提问作者mightycode Newton