--测试数据 CREATE TABLE t_A (ID int,name VARCHAR(6),ParentID int) INSERT INTO t_A SELECT 1,'aa',0 UNION ALL SELECT 2,'bb',0 UNION ALL SELECT 3,'cc',1 UNION ALL SELECT 4,'dd',3
go
--求个节点下所有子节点: create function f_cid(@id int) returns varchar(500) as begin declare @t table(id int,desn varchar(10),parentid int ,lev int) declare @lev int set @lev=1 insert into @t select *,@lev from t_A where id=@id while(@@rowcount>0) begin set @lev=@lev+1 insert into @t select a.*,@lev from t_A a,@t b where a.parentid=b.id and b.lev=@lev-1 end declare @cids varchar(500) select @cids=isnull(@cids+'-','')+ltrim(id) from @t order by lev return @cids end go --自定义函数方式实现子节点查询父节点 if OBJECT_ID('GetParentID') is not null drop function GetParentID go create function GetParentID(@ChildID int) returns varchar(500) as begin declare @t table(ParentID int) insert into @t select ParentID from t_A where ID=@ChildID while @@rowcount<>0 begin insert into @t select a.ParentID from t_A as a inner join @t as b on a.ID=b.ParentID and not exists(select 1 from @t where ParentID=a.ParentID) end declare @pids varchar(500) select @pids=isnull(@pids+'-','')+ltrim(ParentID) from @t return @pids end go
select *,dbo.f_cid(id) as zjd ,dbo.GetParentID(id) as fjd from t_Adrop table t_AID name ParentID zjd fjd ----------- ------ ----------- ------------- 1 aa 0 1-3-4 0 2 bb 0 2 0 3 cc 1 3-4 1-0 4 dd 3 4 3-1-0(4 行受影响)给你个列子查出父节点(fjd)和其子节点(zjd ) 然后分别算出父节点个数*提成%*1000,和子节点个数*提成%*1000 然后用1000 +(子节点个数*提成%*1000)-(父节点个数*提成%*1000) 这样就能算出每个人的奖金了!
--测试数据
CREATE TABLE t_A
(ID int,name VARCHAR(6),ParentID int)
INSERT INTO t_A
SELECT 1,'aa',0
UNION ALL
SELECT 2,'bb',0
UNION ALL
SELECT 3,'cc',1
UNION ALL
SELECT 4,'dd',3
go
--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,desn varchar(10),parentid int ,lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from t_A where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from t_A a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+'-','')+ltrim(id) from @t order by lev
return @cids
end
go
--自定义函数方式实现子节点查询父节点
if OBJECT_ID('GetParentID') is not null drop function GetParentID
go
create function GetParentID(@ChildID int)
returns varchar(500)
as
begin
declare @t table(ParentID int)
insert into @t select ParentID from t_A where ID=@ChildID
while @@rowcount<>0
begin
insert into @t select a.ParentID from t_A as a
inner join @t as b
on a.ID=b.ParentID
and not exists(select 1 from @t where ParentID=a.ParentID)
end
declare @pids varchar(500)
select @pids=isnull(@pids+'-','')+ltrim(ParentID) from @t
return @pids
end
go
select *,dbo.f_cid(id) as zjd ,dbo.GetParentID(id) as fjd from t_Adrop table t_AID name ParentID zjd fjd
----------- ------ ----------- -------------
1 aa 0 1-3-4 0
2 bb 0 2 0
3 cc 1 3-4 1-0
4 dd 3 4 3-1-0(4 行受影响)给你个列子查出父节点(fjd)和其子节点(zjd )
然后分别算出父节点个数*提成%*1000,和子节点个数*提成%*1000
然后用1000 +(子节点个数*提成%*1000)-(父节点个数*提成%*1000)
这样就能算出每个人的奖金了!
子代父代个数都知道了,剩下就是你自己找规律了啊。比如父代几层提多少应该是有规律的,子代几层也是有规律的。根据规律计算呗!