1、用户以及关系表
CREATE TABLE `s_distribution_user` (
`id` bigint(20) NOT NULL COMMENT '用户ID',
`user_name` varchar(30) NOT NULL COMMENT '用户账号',
`user_type` tinyint(4) DEFAULT NULL COMMENT '用户类型(10个人 20商家)',
`phone` varchar(11) DEFAULT '' COMMENT '手机号码',
`avatar` varchar(255) DEFAULT '' COMMENT '头像地址',
`password` varchar(100) DEFAULT '' COMMENT '密码',
`distribution_ratio` int(20) DEFAULT NULL COMMENT '分销比例',
`status` tinyint(1) DEFAULT '0' COMMENT '帐号状态(0正常 1停用)',
`is_del` tinyint(1) DEFAULT '0' COMMENT '删除标志(0代表存在 2代表删除)',
`login_date` datetime DEFAULT NULL COMMENT '最后登录时间',
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
`level` int(11) DEFAULT NULL COMMENT '分销层级(绝对)',
`pid` bigint(20) DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `phone` (`phone`) USING BTREE COMMENT '手机号唯一'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='分销用户表';
# 分销关系
CREATE TABLE `s_distribution_tree_level` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ancestor` bigint(20) NOT NULL COMMENT '祖宗id',
`descendant` bigint(20) NOT NULL COMMENT '后裔id',
`level` int(11) NOT NULL COMMENT '层级',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='分销用户关系表';
2、crud的一些方法
/**
* 新增用户
* inser_id,新增用户id
* resettlement_id,推荐用户id
**/
insert into `s_distribution_treepaths` (ancestor,descendant,path_length)
select ancestor,#{inser_id},level+1 from `s_distribution_treepaths` AS t where t.descendant=#{resettlement_id}
UNION ALL
SELECT #{inser_id},#{inser_id},1;
/**
* 查询所有子孙节点
* 用户id
* 层级level 可选
**/
SELECT * FROM `s_distribution_user` INNER JOIN `s_distribution_treepaths` on `s_distribution_user`.id = `s_distribution_treepaths`.descendant WHERE `s_distribution_treepaths`.ancestor = #{id} and level=#{level};
/**
* 查询所有祖宗节点
* 用户id
**/
SELECT * FROM `s_distribution_user` INNER JOIN `s_distribution_treepaths` on `s_distribution_user`.id =
`s_distribution_treepaths`.ancestor WHERE `s_distribution_treepaths`.descendant = #{id};