求以一存储过程

Question

本帖最后由 liutao132 于 2013-12-25 10:16:09 编辑

Answer 1

文本形式贴测试数据，期待结果，还有说清楚你sqlserver的版本

Answer 2

id  name  score parentid
1    A      0       1
2    B      0       1
3    C      0       2
4    D      0       3
5    E      0       4
6    F      0       5上面是初始化的数据，执行完储存过程之后的数据要为
id  name  score parentid
1    A      88       1
2    B      88       1
3    C      86       2
4    D      84       3
5    E      82       4
6    F      80       5MSSQL2005

Answer 3

----------------------------------------------------------------
-- 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
*/

Answer 4

CREATE TABLE [dbo].[test](
[id] [int] NULL,
[name] [nvarchar](10) NULL,
[score] [int] NULL,
[parentid] [int] NULL
) ON [PRIMARY]GOinsert 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) 我也来贴个

Answer 5

----------------------------------------------------------------
-- 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
DROP  PROC test 
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,CASE WHEN a.[level]=1 THEN b.score ELSE b.score+2 END  AS scroe,a.parentid,a.[level]-1 [level]
FROM cte a INNER JOIN cte2 b ON b.parentid=a.id
WHERE b.[level]>0  --AND a.id<>a.parentid
)
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    88          1
2           B    88          1
3           C    86          2
4           D    84          3
5           E    82          4
6           F    80          5
*/

Answer 6

 你sql里面有  WHERE [level]=5  这个 ， 那么如果数据中的层级达不到，那cte不就没结果了吗

Answer 7

----------------------------------------------------------------
-- 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 
--------------开始查询--------------------------
go
DROP  PROC test 
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,CASE WHEN a.[level]=1 THEN b.score ELSE b.score+2 END  AS scroe,a.parentid,a.[level]-1 [level]
FROM cte a INNER JOIN cte2 b ON b.parentid=a.id
WHERE b.[level]>0  --AND a.id<>a.parentid
)
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    80          1*/ 你sql里面有  WHERE [level]=5  这个 ， 那么如果数据中的层级达不到，那cte不就没结果了吗不会没结果，我只留了两行数据

Answer 8

 你sql里面有  WHERE [level]=5  这个 ， 那么如果数据中的层级达不到，那cte不就没结果了吗不会没结果，我只留了两行数据我的意思就是算出来结果不对啦

Answer 9

如果还有一个节点  比如
id          name score       parentid
----------- ---- ----------- -----------
7           G    0          3
你这个还是有问题的

Answer 10

----------------------------------------------------------------
-- 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 
--------------开始查询--------------------------
go
DROP  PROC test 
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] =(SELECT MAX(CASE WHEN [level]>4 THEN 5 ELSE [level] END)  FROM cte )
UNION ALL 
SELECT a.id,a.NAME,CASE WHEN a.[level]=1 THEN b.score ELSE b.score+2 END  AS scroe,a.parentid,a.[level]-1 [level]
FROM cte a INNER JOIN cte2 b ON b.parentid=a.id
WHERE b.[level]>0  --AND a.id<>a.parentid
)
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    84          1
2           B    84          1
3           C    82          2*/

Answer 11

如果还有一个节点  比如
id          name score       parentid
----------- ---- ----------- -----------
7           G    0          3
你这个还是有问题的加了之后结果是怎样的？

Answer 12

 你sql里面有  WHERE [level]=5  这个 ， 那么如果数据中的层级达不到，那cte不就没结果了吗不会没结果，我只留了两行数据
这个结果应该是不对的吧  这种情况下A的节分应该是82才对。

Answer 13

 你sql里面有  WHERE [level]=5  这个 ， 那么如果数据中的层级达不到，那cte不就没结果了吗不会没结果，我只留了两行数据
这个结果应该是不对的吧  这种情况下A的节分应该是82才对。这个问题看15楼的，

Answer 14

如果还有一个节点  比如
id          name score       parentid
----------- ---- ----------- -----------
7           G    0          3
你这个还是有问题的加了之后结果是怎样的？
是
1 A 88 1
2 B 88 1
3 C 86 2
4 D 84 3
5 E 82 4
6 F 80 5
7 G 80 3
这时候C的积分应该是88才对

Answer 15

如果还有一个节点  比如
id          name score       parentid
----------- ---- ----------- -----------
7           G    0          3
你这个还是有问题的加了之后结果是怎样的？
是
1 A 88 1
2 B 88 1
3 C 86 2
4 D 84 3
5 E 82 4
6 F 80 5
7 G 80 3
这时候C的积分应该是88才对那B和A是不是就是90？

Answer 16

这个楼层的积分也是不对的吧
这种情况下，正确的应该是
id          name score       parentid
----------- ---- ----------- -----------
1           A    84          1
2           B    82          1
3           C    80          2

Answer 17

如果还有一个节点  比如
id          name score       parentid
----------- ---- ----------- -----------
7           G    0          3
你这个还是有问题的加了之后结果是怎样的？
是
1 A 88 1
2 B 88 1
3 C 86 2
4 D 84 3
5 E 82 4
6 F 80 5
7 G 80 3
这时候C的积分应该是88才对那B和A是不是就是90？

是的  应为G是A的四级以内的子节点

Answer 18

你的cte和我的相反，我是从子向父排列，123这样，你是从父向子123排列，那么我从子向上找4级就很容易了

Answer 19

第一步是为了定义一个层级：
;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)
SELECT * from cte
/*
id          name score       parentid    level
----------- ---- ----------- ----------- -----------
1           A    80          1           1
2           B    80          1           2
3           C    80          2           3
4           D    80          3           4
7           G    80          3           4
5           E    80          4           5
6           F    80          5           6
*/

Answer 20

我指的是第一个cte  你的level 是父为1，子让上加，我是子为1，父往上加，当然我是在第二个cte用了聚合才产生的

Answer 21

我是觉得除了末级也就是上面的id=6那个不需要加2之外，上面的即使超过了A的4级，也应该汇总吧？

Answer 22

是的  是对面个节点而言的  我列举的实例数据里也是那样说明的那其实不存在四级这个说法吧？
不明白该怎么描述了  要的结果是：
id  name  score parentid
1    A      90       1
2    B      90       1
3    C      88       2
4    D      84       3
5    E      82       4
6    F      80       5
7    G      80       3

Answer 23

如果是1个节点  应该是下面这样的
id  name  score parentid
1    A      80       12个节点的情况是：
id  name  score parentid
1    A      82       1
2    B      80       13个节点的情况是：
id  name  score parentid
1    A      84       1
2    B      82       1
3    C      80       2下面就不列举了

Answer 24

测了前三个，没问题，不知道数据一多有没有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

Answer 25

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,3)
insert into test values(6,'F',0,5)
insert into test values(7,'G',0,3)
insert into test values(8,'H',0,7)
insert into test values(9,'I',0,8)
select * from test
;with cte2 AS (
SELECT  id,name,score+80  score,parentid,    0 as num
FROM test 
UNION ALL 
SELECT b.id,b.NAME,  a.score     AS scroe,b.parentid, a.num+1 as num
FROM cte2 a INNER JOIN test b ON b.id=a.parentid
WHERE  a.id<>b.id  AND a.num<4
)
 select id ,name,parentid ,( COUNT(id)-1)*2+score  as score from cte2      group by id ,name,score,parentid 
id name score parentid
1 A 0 1
2 B 0 1
3 C 0 2
4 D 0 3
5 E 0 3
6 F 0 5
7 G 0 3
8 H 0 7
9 I 0 8id name parentid score
1 A 1 94
2 B 1 94
3 C 2 92
4 D 3 80
5 E 3 82
6 F 5 80
7 G 3 84
8 H 7 82
9 I 8 80 又写了个，你先看看数据和结果是不是你想要的

Answer 26

1、建表CREATE TABLE [dbo].[Table_2](
[id] [int] NULL,
[name] [varchar](50) NULL,
[score] [int] NULL,
[parentid] [int] NULL
) ON [PRIMARY]GO2、插入测试数据/****** Object:  Database [tempdb1]    Script Date: 2013/12/26 14:06:57 ******/
/****** Object:  Tables [Table_2] ******/
USE [tempdb1]/****** Object:  Table [Table_2] Begin ******/
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(1,'a',92,-1)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(2,'b',92,1)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(3,'c',92,2)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(4,'d',82,3)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(5,'e',88,3)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(6,'f',86,5)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(7,'g',84,6)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(8,'h',82,7)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(9,'i',80,8)
INSERT INTO[Table_2]([id],[name],[score],[parentid])VALUES(10,'j',80,4)
GO
/****** Object:  Table [Table_2] End   ******/3、执行存储过程declare @startid int;
declare @level int;
declare @parents nvarchar(2000)
set @startid = 1;
set @level=0;
set @parents='';
with result  
as
(
 select id, parentid,name,@level as level,@parents as parents,score from Table_2 where id = @startid
 union all
 select a.id,a.parentid,a.name,level+1 as level,cast((','+cast(a.parentid AS nvarchar(2000))+','+parents) as nvarchar(2000)),a.score as parents from Table_2 a join result b on a.parentid = b.id                                                   
)
select * into #tmptable from result;DECLARE @id1 VARCHAR(100), @parents1 VARCHAR(2000),@parentscount1 int,@level1 varchar(100);
set @parentscount1=0;
BEGIN
DECLARE c_test_main CURSOR FAST_FORWARD FOR
SELECT id,parents,level FROM #tmptable;
OPEN c_test_main;
FETCH NEXT FROM c_test_main INTO @id1, @parents1,@level1;
WHILE @@fetch_status = 0
BEGIN
select @parentscount1=COUNT(*) from #tmptable where level-@level1<=4 and level-@level1>0 and CHARINDEX(@id1,parents)!=0;
update Table_2 set score=80+2*@parentscount1 where id=@id1;
FETCH NEXT FROM c_test_main INTO @id1, @parents1,@level1;
END;
CLOSE c_test_main;
DEALLOCATE c_test_main;
END;drop table #tmptable;
go

Answer 27

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,3)
insert into test values(6,'F',0,5)
insert into test values(7,'G',0,3)
insert into test values(8,'H',0,7)
insert into test values(9,'I',0,8)
select * from test
;with cte2 AS (
SELECT  id,name,score+80  score,parentid,    0 as num
FROM test 
UNION ALL 
SELECT b.id,b.NAME,  a.score     AS scroe,b.parentid, a.num+1 as num
FROM cte2 a INNER JOIN test b ON b.id=a.parentid
WHERE  a.id<>b.id  AND a.num<4
)
 select id ,name,parentid ,( COUNT(id)-1)*2+score  as score from cte2      group by id ,name,score,parentid 
id name score parentid
1 A 0 1
2 B 0 1
3 C 0 2
4 D 0 3
5 E 0 3
6 F 0 5
7 G 0 3
8 H 0 7
9 I 0 8id name parentid score
1 A 1 94
2 B 1 94
3 C 2 92
4 D 3 80
5 E 3 82
6 F 5 80
7 G 3 84
8 H 7 82
9 I 8 80 又写了个，你先看看数据和结果是不是你想要的这个完全正确了  非常感谢！！！  我的贴已经结了  不能给你分了。

Answer 28

原先默认初始score都是0现在发现如果不是0会有问题，于是我又改了下
；with cte2 AS (
SELECT  id,name,score,parentid,0 as num
FROM test 
UNION ALL 
SELECT b.id,b.NAME,a.score AS scroe,b.parentid, a.num+1 as num
FROM cte2 a INNER JOIN test b ON b.id=a.parentid
WHERE  a.id<>b.id  AND a.num<4
)
select a.id ,name, parentid ,a.score+80+b.total score from test a inner join (
 select id  ,( COUNT(id)-1)*2 total from cte2  group by id )b  on a.id=b.id