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

2022-08-06

三级分销已是一个越来越常见的需求。如何去获取到分销商的下级?如何去获取上级分销商?这里提供几条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;

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

为方便下面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查所有父级或子级

本文地址:https://blog.csdn.net/weixin_38125045/article/details/107318127