查询无限级/三级分销的简单易用SQL

三级分销已是一个越来越常见的需求。如何去获取到分销商的下级？如何去获取上级分销商？这里提供几条sql语句，无需创建存储过程/函数，简单易用。

本文中，采用邻接表的方式来进行处理，什么是邻接表？有没有其他处理方法？那看这篇文章去：mysql（多级分销）无限极数据库设计方法

邻接表结构

设计表如下，实际场景可根据需要自己更改：

CREATE TABLE `distribution_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pid` int(11) unsigned NOT NULL COMMENT '父代id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

其中，id和pid是最为重要的，字段名可以不同，当这两者却必须存在，其他字段可自行增减。

为方便下面sql查询结果展示，现在给这表加入了下面数据：

id	name	pid
1	一代	0
2	二代1	1
3	二代2	1
4	三代1 (二代1的仔)	2
5	三代2 (二代1的仔)	2
6	四代1 (三代1的仔)	4
7	四代2 (三代2的仔)	5
8	五代1 (四代1的仔)	6
9	六代1 (五代1的仔)	8

下面就正式开始我们的sql之旅吧！

查询数据

一、根据子节点查询上面所有的父节点

搜索 六代1 的所有父级（六代1 的id为9）为例：

@r := 9 设置自己所要搜索子节点的id

SELECT
	T2.* 
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := pid FROM `distribution_user` WHERE id = _id ) AS 2v2,
		@l := @l + 1 AS lvl 
	FROM
		( SELECT @r := 9 ) vars, -- 查询id为9的所有上级
		distribution_user h 
	WHERE
		@r <> - 1 
	) T1
	JOIN distribution_user T2 ON T1._id = T2.id 
ORDER BY
	id ASC;

注意：上面查询的子节点包括了它自身

根据上面查询子节点所有的父节点方法，改装一下，得到最常用到的三级分销：最近三代父节点。

二、根据子节点查询最近3代父节点

搜索 六代1 的最近3代父级（六代1 的id为9）为例：

@r := 9 设置自己所要搜索子节点的id

SELECT
	T2.*,
	T1.plevel
FROM
	(
	SELECT
		@r AS _id,
		( SELECT @r := pid FROM `distribution_user` WHERE id = _id ) AS pid,
		@l := @l + 1 AS plevel 
	FROM
		( SELECT @r := 9 ) vars,	-- 查询id为9的所有上级
		( SELECT @l :=- 1 ) plevel,
		distribution_user h 
	WHERE
		@r <> - 1 
	) T1
	JOIN distribution_user T2 ON T1._id = T2.id 
WHERE
	T1.plevel > 0 
	AND T1.plevel <= 3 
ORDER BY
	id ASC;

其中，别名T2的表示原本的数据，而T1中的plevel即是父代的级别（1：直接父代，2：二级父代，3：三级父代）

如果plevel为0，代表是查询的节点自身。

三、根据父节点查询出下面所有的子节点

搜索三代1的所有子孙（三代1的id为4）为例：

@p := 4 设置自己所要搜索父代的id

SELECT
	t3.* 
FROM
	(
	SELECT
		*,
	IF
		( find_in_set( t1.pid, @p ), @p := concat( @p, ',', id ), 0 ) AS child_id 
	FROM
		( SELECT * FROM distribution_user t ORDER BY id ) t1,
		( SELECT @p := 4 ) t2 
	) t3 
WHERE
	child_id != 0;

上面的方式无疑可以查询到所有的子节点，但是无法知道其代数，并别提获取最近3代是哪些了。结合根据子节点查询最近3代父节点方法，完成所需要的获取代数，并且可进行筛选的方法。

四、根据父节点查询最近3代子节点

搜索三代1的最近3代子孙（三代1的id为4）为例：

@r := 4 设置自己所要搜索父代的id

SELECT
	T2.*,
	T1.clevel 
FROM
	(
	SELECT
		@r AS _pid,
		(
		SELECT
			@r := group_concat( id ) 
		FROM
			`distribution_user` 
		WHERE
		FIND_IN_SET( pid, _pid )) AS cid,
		@l := @l + 1 AS clevel 
	FROM
		( SELECT @r := 4 ) vars,-- 查询id为4的所有子节点
		( SELECT @l := 0 ) clevel,
		distribution_user h 
	WHERE
		@r IS NOT NULL 
	) T1
	INNER JOIN distribution_user T2 ON FIND_IN_SET( T2.pid, T1._pid ) 
WHERE
	T1.clevel <= 3 
ORDER BY
	id ASC;

参考文献：mysql 根据id查所有父级或子级