三级分销数据库设计

一，数据结构

二，查询方法

1.mysql递归查询

• 获取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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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

其中，别名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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

@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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

2. 使用中间关系表
   递归调用虽然也能处理，但是数据量大了很容易产生性能问题。如果再统计每个级别下会员的消费，收入统计时，需要和消费表关系查询，那就耗时更长。换个思路来，我再创建个表，记录用户等级从属关系，然后在生成新用户的时候插入一条新的关系记录就可以了。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
   再往上找userid=2的数据的pid为1
   在relation中插入一条数据 pid=1，cid=11，plevel=2
   
   查询用户1的所有下级，查出来的cid列就是结果

select * from sj_relation where pid=1
1

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

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

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

select * from sj_relation where cid=10
1

假如假设一级用户返佣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
1

某些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
1

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
1