在SQL中展现父子关系的查询方法与实现步骤

SQL中展现父子关系的查询方法主要用于查询树形结构的数据，比如组织架构、商品分类等。实现步骤如下：

1. 创建表

CREATE TABLE IF NOT EXISTS category (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  parent_id INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

2. 插入数据

INSERT INTO category (name, parent_id) VALUES ('电脑', 0);
INSERT INTO category (name, parent_id) VALUES ('笔记本', 1);
INSERT INTO category (name, parent_id) VALUES ('台式机', 1);
INSERT INTO category (name, parent_id) VALUES ('苹果', 2);
INSERT INTO category (name, parent_id) VALUES ('华硕', 2);
INSERT INTO category (name, parent_id) VALUES ('戴尔', 3);
INSERT INTO category (name, parent_id) VALUES ('惠普', 3);

3. 查询数据

查询父子关系数据的常用方法有两种：连接查询和递归查询。

3.1 连接查询

SELECT 
  c1.name AS '父类',
  c2.name AS '子类'
FROM 
  category AS c1
  LEFT JOIN category AS c2 ON c1.id = c2.parent_id;

3.2 递归查询

WITH RECURSIVE cte AS (
  SELECT
    id,
    name,
    parent_id
  FROM
    category
  WHERE
    parent_id = 0
  UNION ALL
  SELECT
    c.id,
    c.name,
    c.parent_id
  FROM
    category c
  INNER JOIN cte ON cte.id = c.parent_id
)
SELECT
  c1.name AS '父类',
  c2.name AS '子类'
FROM
  cte c1
  LEFT JOIN cte c2 ON c1.id = c2.parent_id;

以上就是SQL中展现父子关系的查询方法与实现步骤，可以根据实际需求选择合适的查询方法，以达到更好的查询效果。