讨论数量:
方法1,会员表加字段relation
,保存推荐关系,如:
|,1,2,3,
| => 1推荐2,2推荐3
|,1,2,5,
| => 1推荐2,2推荐5
查询2
的所有1级:
where parent_id =2
查询2
的所有2级:
where relation like '%,2,%' and parent_id <>2
查询2
的所有下级:
where relation like '%,2,%'
缺点,代数过多时,查询时不好按代数查询。
方法2,用一张表保存关系
CREATE TABLE `member_relation` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`member_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '会员id',
`ancestor_id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '上级id',
`deep` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '代数',
`member_level` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '会员等级',
`ancestor_level` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' COMMENT '上级等级',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_member_id` (`member_id`) USING BTREE,
INDEX `idx_ancestor_id` (`ancestor_id`) USING BTREE
)
COMMENT='会员层级关系'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
推荐文章: