SQL单表实现分类管理及层级展示查询方案咨询

没问题，我来帮你搞定这个单表分类系统的需求，从表设计到操作语句再到查询展示，一步到位：

1. 分类表结构设计 #

我们用自关联单表来实现层级分类，完全不需要额外的关联表。表中通过parent_id字段关联自身的id来实现父子层级；同时设置分类名称唯一约束，外键级联删除保证删父分类时自动移除所有子分类。

创建表的SQL语句：

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL UNIQUE,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

• UNIQUE约束确保分类名称不会重复，重复插入会直接触发数据库报错
• ON DELETE CASCADE是核心配置：当父分类被删除时，所有关联的子分类会被自动级联删除
• parent_id为NULL时，表示这是一个主分类

2. 核心操作实现 #

2.1 新增分类 #

新增主分类（无父分类）

INSERT INTO categories (category_name, parent_id) VALUES ('House', NULL);

新增子分类（指定父分类）

比如给House添加子分类Flat（假设House的id是1）：

INSERT INTO categories (category_name, parent_id) VALUES ('Flat', 1);

2.2 修改分类 #

修改分类名称或父分类（注意名称不能和现有分类重复）：

-- 修改分类名称（确保新名称唯一）
UPDATE categories 
SET category_name = '2BHK Flat Updated' 
WHERE id = 3;

-- 修改父分类（比如把某个子分类移到另一个父分类下）
UPDATE categories 
SET parent_id = 2 
WHERE id = 4;

如果要避免修改名称时和其他分类冲突，可以加额外校验条件：

UPDATE categories 
SET category_name = '2BHK Flat Updated' 
WHERE id = 3 
AND NOT EXISTS (SELECT 1 FROM categories WHERE category_name = '2BHK Flat Updated' AND id != 3);

2.3 删除分类 #

直接删除父分类，所有子分类会被自动级联删除：

-- 删除House分类，所有子分类（Flat、2BHK Flat等）都会被同步删除
DELETE FROM categories WHERE id = 1;

3. 查询所有分类及对应父分类的SQL #

用自左连接（LEFT JOIN）关联自身，获取每个分类的父分类名称，同时处理主分类无父分类的情况：

SELECT 
    c.id AS 'ID',
    c.category_name AS '分类',
    COALESCE(p.category_name, '主分类') AS '父分类'
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id
ORDER BY c.id;

• COALESCE函数会把NULL的父分类名称替换为“主分类”，让结果更直观
• ORDER BY c.id可以保持分类的创建顺序，也可以根据需求调整为按层级排序

4. 示例分类层级展示 #

按照你给出的层级，对应的表格如下：

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