当前位置: 代码迷 >> Sql Server >> 求以一存储过程,该怎么处理
  详细解决方案

求以一存储过程,该怎么处理

热度:32   发布时间:2016-04-24 18:41:09.0
求以一存储过程
数据表结构
id  name score(积分) parentid(和id是对应的,体现层级关系)

现在的需求是:
要把每个用户的score=score+80的同时把该用户的四级以内的每个上级的积分score=score+2;

举例说明
A是根节点  B是A的子节点  C是B的子节点  D是C的子节点   E是D的子节点  F是E的子节点(假设每个用户的初始score为0)
对于A:score=0+80=80;同时把他的四级以内的上级节点score=score+2  A没有上级节点  不用操作了
对于B:score=0+80=80;同时把他的四级以内的上级节点score=score+2,即此时A的score=80+2=82;
对于C:score=0+80=80;同时把他的四级以内的上级节点score=score+2,即此时A的score=82+2=84,B的score=80+2=82;
对于D: score=0+80=80;同时把他的四级以内的上级节点score=score+2,即此时A的score=84+2=86,B的score=82+2=84,C的score=80+2=82;
对于E:score=0+80=80;同时把他的四级以内的上级节点score=score+2,即此时A的score=86+2=88,B的score=84+2=86,C的score=82+2=84,D的score=80+2=82;
对于F:score=0+80=80;同时把他的四级以内的上级节点score=score+2,即此时B的score=86+2=88,C的score=84+2=86,D的score=82+2=84,E的score=80+2=82;由于A是F的第五级上级,所以F的存在不会影响A的score
------解决方案--------------------
----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-12-25 10:29:34
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
-- Dec 28 2012 20:23:12 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([id] int,[name] varchar(1),[score] int,[parentid] int)
insert [huang]
select 1,'A',0,1 union all
select 2,'B',0,1 union all
select 3,'C',0,2 union all
select 4,'D',0,3 union all
select 5,'E',0,4 union all
select 6,'F',0,5
--------------开始查询--------------------------
go
CREATE PROC test 
AS 
;WITH cte AS (
select id    ,      name,80 AS  score  ,     parentid ,1 [level]
from [huang]
WHERE id=parentid
UNION ALL 
SELECT a.id,a.NAME,b.score+a.score AS score,a.parentid,b.[level]+1 AS [level]
FROM huang a INNER JOIN cte b ON a.parentid=b.id
WHERE a.id>b.id
),
cte2 AS (
SELECT id,name,score+2 score,parentid,[level]
FROM cte 
WHERE [level]=5
UNION ALL 
SELECT a.id,a.NAME,b.score+2 AS scroe,a.parentid,a.[level]-1 [level]
FROM cte a INNER JOIN cte2 b ON b.parentid=a.id
WHERE b.[level]>1 
)
SELECT id,name,score,parentid FROM cte2
UNION ALL 
SELECT id,name,score,parentid FROM cte
WHERE id NOT IN (SELECT id FROM cte2)
ORDER BY id


go 
EXEC test 

----------------结果----------------------------
/* id          name score       parentid
----------- ---- ----------- -----------
1           A    80          1
2           B    88          1
3           C    86          2
4           D    84          3
5           E    82          4
6           F    80          5
*/

------解决方案--------------------



CREATE TABLE [dbo].[test](
[id] [int] NULL,
[name] [nvarchar](10) NULL,
[score] [int] NULL,
[parentid] [int] NULL
) ON [PRIMARY]

GO

insert into test values(1,'A',0,1)
insert into test values(2,'B',0,1)
insert into test values(3,'C',0,2)
insert into test values(4,'D',0,3)
insert into test values(5,'E',0,5)
insert into test values(6,'F',0,5)
 

;WITH cte AS (
select id,name,80+score AS score  ,parentid ,1 [level]
from test
UNION ALL 
SELECT a.id,a.NAME,b.score+a.score AS score,a.parentid,b.[level]+1 AS [level]
FROM test a INNER JOIN cte b ON a.id=b.parentid
where b.id<>b.parentid 
) ,
cte1 as ( 
select id ,name,score,parentid,MAX([level])[level] from cte group by id ,name,score,parentid
  相关解决方案