三级分销（栏目无限极分类）数据库设计

一，第一种数据结构

1. 查询方法，递归查询

- 获取id9的所有上级

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

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

- 查询子节点最上三级的父节点

其中，别名T2的表示原本的数据，而T1中的plevel即是父代的级别（1：直接父代，2：二级父代，3：三级父代）如果plevel为0，代表是查询的节点自身。

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

- 父节点查询出下面所有的子节点

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

- 查询节点的最近三代子节点

@r := 1 为要查询的数据id

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

二，第二种数据结构，使用中间关系表

递归调用虽然也能处理，但是数据量大了很容易产生性能问题。如果再统计每个级别下会员的消费，收入统计时，需要和消费表关系查询，那就耗时更长。换个思路来，我再创建个表，记录用户等级从属关系，然后在生成新用户的时候插入一条新的关系记录就可以了。pid是父级id，cid是子id，plevel是父级是子级的几层关系。
关系表是在用户注册时产生的，且核心也是以新插入数据为基点，向上找。
例如：用户id=2，名称为“二级1-1 ”，发展了一个下级。user表中新增一个用户自增主键id为11，通过推荐码找自己的上级是“二级1-1”且userid=2，
对应关系就是user2增加了一个一级子11，user1增加了一个二级子11
在relation中插入一条数据 pid=2，cid=11，plevel=1，意思就是子id11是父id2的一级子
再往上找userid=2的数据的pid为1
在relation中插入一条数据 pid=1，cid=11，plevel=2，意思就是子id11是父id1的二级子

• 查询用户1的所有下级，查出来的cid列就是结果

select * from sj_relation where pid=1

• 查询所有用户4的一级下线，查出来的cid列就是结果

select * from sj_relation where pid = 4 and plevel = 1;

• 查询用户10的所有父级，查出来的pid列就是结果

select * from sj_relation where cid=10

• 假如假设一级用户返佣10%，二级用户返佣5%，计算四号用户的所有返佣

SELECT sum(DECODE(r.plevel,1,o.amount*0.1,2,o.amount*0.05)) AS rebate_amount FROM sj_relation r, sj_order o WHERE o.user_id = r.cid AND r.pid = '4' and r.plevel > 0

某些mysql版本会报错

SELECT r.*,case when r.plevel=1 then o.amount*0.1 when r.plevel=2 then o.amount*0.05 else 0 end as backmoney from sj_relation r,sj_order o WHERE o.user_id = r.cid AND r.pid = '4' and r.plevel > 0

SELECT sum(case when r.plevel=1 then o.amount*0.1 when r.plevel=2 then o.amount*0.05 else 0 end) from sj_relation r,sj_order o WHERE o.user_id = r.cid AND r.pid = '4' and r.plevel > 0 GROUP BY r.pid

第三种数据结构

• 获取张三所有下级

select * from user where path like "-1-%";

• 获取张三所有二级下级

select * from user where pid=1;

• 获取张三所有孙子

select * from user where level=2 and path like "-1%";

• 获取张三二孙子所有上级

select * from user where id=9;

查出来的path值使用 array_filter(explode('-', trim($path, '-'))); 即可获得父级数组

• 计算张三所有返佣
  假如假设一级用户返佣10%，二级用户返佣5%，张三儿子花费500，张三大孙子花费1000，张三二孙子花费2500

//先获取所有二级子，然后再计算
select * from user where pid = 1;
select SUM(amount * 0.1) from orders where user_id in (2);
//获取所有三级子，然后再计算
select * from user where level=2 and path like "-1-%";
select SUM(amount * 0.05) from orders where user_id in (4,9);