求以一存储过程
本帖最后由 liutao132 于 2013-12-25 10:16:09 编辑 数据表结构
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
)
select
id,
name,
case when [level]>4 then score+8
else score+([level]-1)*2
end score,
parentid from cte1 OPTION (MAXRECURSION 0)
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 UNION ALL
SELECT 7,'G',0,3
;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 AS score,parentid,[level]
from cte
WHERE [level]=(SELECT MAX([level])-1 FROM cte )
UNION ALL
SELECT a.id,a.NAME, b.score+2 AS score,a.parentid,a.[level] [level]
FROM cte a INNER JOIN cte2 b ON a.[level]=b.[level]-1
WHERE b.[level]>1
)
SELECT DISTINCT id,name,score,parentid-- ,[level]
FROM cte2
UNION ALL
SELECT id,name,score,parentid
FROM cte
WHERE id NOT IN (SELECT DISTINCT id FROM cte2)
ORDER BY id