Web编程 订阅所有【Web编程】的日志

会员推荐系统中数据统计和表结构设计

编辑:dnawo 日期:2013-07-23

字体大小: 小 中 大
系统会员采用推荐方式，比如A推荐B，B推荐C、D、E，E推荐F，...，要求能统计出某个会员发展的所有会员数以及第N级会员数(例如C发展的第3级会员数)。



第一种方案

①.表结构

复制内容到剪贴板程序代码 程序代码
create table MZ_User
(
    Id int identity(1,1) primary key,
    Name nvarchar(20),
    Fid int --上级编号
)

示例数据：
引用内容 引用内容
insert into MZ_User select 'a',0
insert into MZ_User select 'b',1
insert into MZ_User select 'c',2
insert into MZ_User select 'd',2
insert into MZ_User select 'e',4
insert into MZ_User select 'f',4
insert into MZ_User select 'g',4
insert into MZ_User select 'h',0

②.数据统计

复制内容到剪贴板程序代码 程序代码
--第3级会员数
select count(*) from (select Id from MZ_User where Fid in(select Id from MZ_User where Fid in(select Id from MZ_User where Fid=1))) as tab

③.方案缺点

不易统计出某个会员发展的所有会员数，统计第N级会员数时子查询可能过长，语句也不易编写。

第二种方案

①.表结构

复制内容到剪贴板程序代码 程序代码
create table MZ_User
(
    Id int identity(1,1) primary key,
    Name nvarchar(20),    
    TreeId int, --层级
    UPath nvarchar(100) --层级路径
)

示例数据：
引用内容 引用内容
insert into MZ_User select 'a',0,''
insert into MZ_User select 'b',1,'1,'
insert into MZ_User select 'c',2,'1,2,'
insert into MZ_User select 'd',2,'1,2,'
insert into MZ_User select 'e',3,'1,2,4,'
insert into MZ_User select 'f',3,'1,2,4,'
insert into MZ_User select 'g',3,'1,2,4,'
insert into MZ_User select 'h',0,''

②.数据统计

复制内容到剪贴板程序代码 程序代码
declare @id int, @treeid int, @upath nvarchar(200)
select @id=Id,@treeid=TreeId,@upath=UPath from MZ_User where Id=1
set @upath=@upath+cast(@id as nvarchar)+','
--所有会员数
select COUNT(*) from MZ_User where UPath like @upath+'%'
--第3级会员数
select count(*) from MZ_User where TreeId=@treeid+3 and UPath like @upath+'%'

③.方案缺点

UPath字段可能过长。

第三种方案

①.表结构

复制内容到剪贴板程序代码 程序代码
create table MZ_User
(
    Id int identity(1,1) primary key,
    Name nvarchar(20),    
    TreeId int, --层级
    Lid int, --左值
    Rid int --右值
)

示例数据：
引用内容 引用内容
insert into MZ_User select 'root',0,1,18
insert into MZ_User select 'a',1,2,15
insert into MZ_User select 'b',2,3,14
insert into MZ_User select 'c',3,4,5
insert into MZ_User select 'd',3,6,13
insert into MZ_User select 'e',4,7,8
insert into MZ_User select 'f',4,9,10
insert into MZ_User select 'g',4,11,12
insert into MZ_User select 'h',1,16,17



②.数据统计

复制内容到剪贴板程序代码 程序代码
declare @treeid int, @lid int, @rid int
select @treeid=TreeId,@lid=Lid,@rid=Rid from MZ_User where Id=2
--所有会员数
select COUNT(*) from MZ_User where Lid>@lid and Rid<@rid
--第3级会员数
select COUNT(*) from MZ_User where TreeId=@treeid+3 and Lid>@lid and Rid<@rid

③.方案说明

·方案要求有且只有一个根节点；
·增加记录时，大于上级会员左值的左右值+2，新记录左值为上级会员左值+1，右值为上级会员左值+2；
·删除记录时，删除当前节点及子节点，大于当前节点右值的左右值-(当前节点右值-当前节点左值+1)；

④.方案缺点

增删记录要全表查找并更新左右值，若表数据量较大，会非常耗时。用一张22w条记录的表做测试，下边两条语句平均耗时4800ms(Lid和Rid有建索引)：

复制内容到剪贴板程序代码 程序代码
update MZ_User set Lid=Lid+2
update MZ_User set Rid=Rid+2


资料参考

[1].无限级分类实现思路：http://www.cnblogs.com/yangmanyan/archive/2011/06/16/2082963.html

上一篇: SQL Server 2008作业失败：无法确定所有者是否有服务器访问权限
下一篇: 福建协和医院抗A效价/抗B效价检测流程
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
最新日志:
评论: 0 | 引用: 0 | 查看次数: 4904
发表评论
请登录后再发表评论！