具有父ID和子ID的SQL Server层次结构 [英] SQL Server hierarchy with parent id and child id
本文介绍了具有父ID和子ID的SQL Server层次结构的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想生成用红色表示的层次结构ID"值.该值是层次结构中最低的id级别.最高层次为所有服务组"，最低层次为"LL800_GB05".它们与父母ID和ID有关.随附的图像只是一个样本集.
I would like to generate the 'hierarchy id' values which is denoted in red. The value is the lowest level of id in the hierarchy. The highest level of hierarchy is 'ALL Service Groups' and the lowest level is 'LL800_GB05'. They are related with Parent ID and ID. The attached image is only a sample set.
更新-我已经建立了关系和血统.现在我知道Hierarchy的最高成员.我想为新列中的每一行关联最低的childID.我厌倦了在Parentid和ID上进行内部联接，但是没有完全锻炼.我尝试了交叉连接，但没有锻炼.以下是要插入的数据
Update-- I have formed a relationship and lineage. Now i know the highest member of Hierarchy.I want to associate the lowest childID for each row in a new column. I tired doing an inner join on Parentid and ID but didnot workout completely. I tried a crossjoin but didnot workout. Below is the data for insertion
CREATE TABLE CO1 (
PARENTID INT,
ID INT,
CID VARCHAR(38) ,
Depth INT,
Lineage VARCHAR(28)
);
INSERT INTO CO1 VALUES (1105,48039,'All Service Groups',0,'/');
INSERT INTO CO1 VALUES (48039,48100,'Ovhd Service Groups',1,'/48039/');
INSERT INTO CO1 VALUES (48039,133686,'Network Services',1,'/48039/');
INSERT INTO CO1 VALUES (48039,133689,'Shared Services',1,'/48039/');
INSERT INTO CO1 VALUES (48039,133692,'SW Security K-12 Monitoring',1,'/48039/');
INSERT INTO CO1 VALUES (48039,133695,'Print Services Group',1,'/48039/');
INSERT INTO CO1 VALUES (48039,138170,'Miscellaneous Service Groups',1,'/48039/');
INSERT INTO CO1 VALUES (48039,48109,'Total Shared Services Security',1,'/48039/');
INSERT INTO CO1 VALUES (48100,140656,'K909_GB00',2,'/48039/48100/');
INSERT INTO CO1 VALUES (48100,140631,'K909_GG00',2,'/48039/48100/');
INSERT INTO CO1 VALUES (48100,140634,'K909_GA02',2,'/48039/48100/');
INSERT INTO CO1 VALUES (48100,48045,'G0000002',2,'/48039/48100/');
INSERT INTO CO1 VALUES (48109,48089,'GH010001',2,'/48039/48109/');
INSERT INTO CO1 VALUES (48109,48090,'GH010002',2,'/48039/48109/');
INSERT INTO CO1 VALUES (48109,48091,'GH010003',2,'/48039/48109/');
INSERT INTO CO1 VALUES (48109,48092,'GH010004',2,'/48039/48109/');
INSERT INTO CO1 VALUES (48109,48093,'GH010005',2,'/48039/48109/');
INSERT INTO CO1 VALUES (48109,48094,'GH010006',2,'/48039/48109/');
INSERT INTO CO1 VALUES (133686,133647,'Network Voice Services',2,'/48039/133686/');
INSERT INTO CO1 VALUES (133686,48106,'Network Data Services',2,'/48039/133686/');
INSERT INTO CO1 VALUES (133689,133634,'Server Hosting Services',2,'/48039/133689/');
INSERT INTO CO1 VALUES (133689,48105,'Mainframe Services',2,'/48039/133689/');
INSERT INTO CO1 VALUES (133689,133640,'Desktop Support & Application Services',2,'/48039/133689/');
INSERT INTO CO1 VALUES (133692,133682,'K12 Monitoring',2,'/48039/133692/');
INSERT INTO CO1 VALUES (133695,48108,'Total Print Services',2,'/48039/133695/');
INSERT INTO CO1 VALUES (133695,140641,'K909_GZ00',2,'/48039/133695/');
INSERT INTO CO1 VALUES (138170,138165,'Help Desk',2,'/48039/138170/');
INSERT INTO CO1 VALUES (138170,138166,'DS',2,'/48039/138170/');
INSERT INTO CO1 VALUES (138170,138167,'SCS',2,'/48039/138170/');
INSERT INTO CO1 VALUES (138170,138168,'IT Plan and Admin',2,'/48039/138170/');
INSERT INTO CO1 VALUES (138170,138162,'K909XGA01',2,'/48039/138170/');
INSERT INTO CO1 VALUES (138170,138163,'Surplus',2,'/48039/138170/');
INSERT INTO CO1 VALUES (133682,133681,'K909_GM00',3,'/48039/133692/133682/');
INSERT INTO CO1 VALUES (133682,133683,'K909_GM01',3,'/48039/133692/133682/');
INSERT INTO CO1 VALUES (133682,133684,'K909XGM01',3,'/48039/133692/133682/');
INSERT INTO CO1 VALUES (133647,133657,'Telecom Project Management',3,'/48039/133686/133647/');
INSERT INTO CO1 VALUES (133647,133660,'Telecom Wiring',3,'/48039/133686/133647/');
INSERT INTO CO1 VALUES (133647,133663,'Shared CENTREX',3,'/48039/133686/133647/');
INSERT INTO CO1 VALUES (133647,133649,'Total VoiP',3,'/48039/133686/133647/');
INSERT INTO CO1 VALUES (133647,133651,'Local Services Telco',3,'/48039/133686/133647/');
INSERT INTO CO1 VALUES (133647,133654,'Long Distance Telco',3,'/48039/133686/133647/');
INSERT INTO CO1 VALUES (133634,48076,'K909_GG40',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48077,'K909_GG41',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48078,'K909_GG42',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48079,'K909_GG43',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48080,'K909_GG44',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48081,'K909_GG45',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48082,'K909_GG46',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,48083,'K909_GG47',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,133638,'K909_GG48',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,133679,'K909_GG49',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,133680,'K909_GG07',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133634,138164,'K909_GG04',3,'/48039/133689/133634/');
INSERT INTO CO1 VALUES (133640,133642,'Application Services',3,'/48039/133689/133640/');
INSERT INTO CO1 VALUES (133640,133644,'Total Desktop Support',3,'/48039/133689/133640/');
INSERT INTO CO1 VALUES (48105,48067,'K909_GG11',3,'/48039/133689/48105/');
INSERT INTO CO1 VALUES (48105,48068,'K909_GG12',3,'/48039/133689/48105/');
INSERT INTO CO1 VALUES (48105,48069,'K909_GG13',3,'/48039/133689/48105/');
INSERT INTO CO1 VALUES (48105,48070,'K909_GG14',3,'/48039/133689/48105/');
INSERT INTO CO1 VALUES (48105,48071,'K909_GG10',3,'/48039/133689/48105/');
INSERT INTO CO1 VALUES (48106,133667,'Total DTO Internet',3,'/48039/133686/48106/');
INSERT INTO CO1 VALUES (48106,133670,'MetroNet',3,'/48039/133686/48106/');
INSERT INTO CO1 VALUES (48106,133673,'Router Management',3,'/48039/133686/48106/');
INSERT INTO CO1 VALUES (48106,133676,'Firll / VN Management',3,'/48039/133686/48106/');
INSERT INTO CO1 VALUES (48106,48103,'Total WAN',3,'/48039/133686/48106/');
INSERT INTO CO1 VALUES (48108,48095,'K909_GK00',3,'/48039/133695/48108/');
INSERT INTO CO1 VALUES (48103,48066,'K909_GD05',4,'/48039/133686/48106/48103/');
INSERT INTO CO1 VALUES (133642,48074,'K909_GG30',4,'/48039/133689/133640/133642/');
INSERT INTO CO1 VALUES (133644,133678,'K909_GG21',4,'/48039/133689/133640/133644/');
INSERT INTO CO1 VALUES (133644,48072,'K909_GG20',4,'/48039/133689/133640/133644/');
INSERT INTO CO1 VALUES (133649,48053,'K909_GB05',4,'/48039/133686/133647/133649/');
INSERT INTO CO1 VALUES (133651,48054,'K909_GB06',4,'/48039/133686/133647/133651/');
INSERT INTO CO1 VALUES (133654,48055,'K909_GB07',4,'/48039/133686/133647/133654/');
INSERT INTO CO1 VALUES (133657,48049,'K909_GB01',4,'/48039/133686/133647/133657/');
INSERT INTO CO1 VALUES (133657,133656,'K909_GB08',4,'/48039/133686/133647/133657/');
INSERT INTO CO1 VALUES (133660,133659,'K909_GB09',4,'/48039/133686/133647/133660/');
INSERT INTO CO1 VALUES (133660,48050,'K909_GB02',4,'/48039/133686/133647/133660/');
INSERT INTO CO1 VALUES (133663,48051,'K909_GB03',4,'/48039/133686/133647/133663/');
INSERT INTO CO1 VALUES (133663,48052,'K909_GB10',4,'/48039/133686/133647/133663/');
INSERT INTO CO1 VALUES (133667,48056,'K909_GD01',4,'/48039/133686/48106/133667/');
INSERT INTO CO1 VALUES (133667,48057,'K909_GD07',4,'/48039/133686/48106/133667/');
INSERT INTO CO1 VALUES (133670,48058,'K909_GD02',4,'/48039/133686/48106/133670/');
INSERT INTO CO1 VALUES (133673,48060,'K909_GD03',4,'/48039/133686/48106/133673/');
INSERT INTO CO1 VALUES (133676,48061,'K909_GD04',4,'/48039/133686/48106/133676/');
INSERT INTO CO1 VALUES (133676,48062,'K909_GD06',4,'/48039/133686/48106/133676/');
我尝试过的SQL语句如下
and SQL statements i tried are below
SELECT A.[PARENTID]
,A.[ID]
,A.[CID]
,A.[Depth]
,A.[Lineage],B.ID as HierID
FROM [dbo].[CO1new] A
inner join [dbo].[CO1new] as B on (A.ID =B.PARENTID)
where A.PARENTID in ( 1105,133686,133647,133657,133660,133663,133649,133651,133654)
order by 5
请输入
谢谢
推荐答案
根据您的要求.这是构建完整层次结构的代码.
此结果是根据您在问题中提供的85行层次结构构建的.
As per your request. Here is the code to build the full hierarchy.
This results were constructed from the 85 rows of hierarchy that you supplied in your question.
我仍然不知道您所说的最低水平.有许多最低级别.我添加了一个字段IsParent，该字段指示一个点是否是父节点/汇总节点.零表示叶子节点或底部节点.
I still don't know what you mean by Lowest Level. There are many lowest levels. I added a field IsParent which indicates whether or not a point is a parent/roll-up... Zero indicates a leaf or bottom node.
;With cteOH (id,ParentID,Lvl,CID,SortSeq)
as (
Select id,ParentID,Lvl=1,CID,SortSeq = cast(concat(id,'>') as varchar(500)) from CO1 where ParentID=1105
Union All
Select h.id,h.ParentID,cteOH.Lvl+1,h.CID ,SortSeq = cast(concat(cteOH.SortSeq,h.id)+'>' as varchar(500)) FROM CO1 h INNER JOIN cteOH ON h.ParentID = cteOH.id
),
cteR1 as (Select id,SortSeq,r1=Row_Number() over (Order by SortSeq) From cteOH),
cteR2 as (Select A.id,r2 = max(B.r1) From cteOH A Join cteR1 B on (B.SortSeq Like A.SortSeq+'%') Group By A.id)
Select B.R1
,C.R2
,A.Lvl
,A.ID
,A.ParentID
,A.CID
,IsParent = iif(R1<>R2,1,0)
Into dbo.CO1_Hier -- << Storing Results of Hier For Ease of Use
From cteOH A
Join cteR1 B on (A.ID=B.ID)
Join cteR2 C on (A.ID=C.ID)
层次结构构建的结果
R1 R2 Lvl ID ParentID CID IsParent
-------------------- -------------------- ----------- ----------- ----------- -------------------------------------- -----------
1 85 1 48039 1105 All Service Groups 1
2 31 2 133686 48039 Network Services 1
3 18 3 133647 133686 Network Voice Services 1
4 5 4 133649 133647 Total VoiP 1
5 5 5 48053 133649 K909_GB05 0
6 7 4 133651 133647 Local Services Telco 1
7 7 5 48054 133651 K909_GB06 0
8 9 4 133654 133647 Long Distance Telco 1
9 9 5 48055 133654 K909_GB07 0
10 12 4 133657 133647 Telecom Project Management 1
11 11 5 133656 133657 K909_GB08 0
12 12 5 48049 133657 K909_GB01 0
13 15 4 133660 133647 Telecom Wiring 1
14 14 5 133659 133660 K909_GB09 0
15 15 5 48050 133660 K909_GB02 0
16 18 4 133663 133647 Shared CENTREX 1
17 17 5 48051 133663 K909_GB03 0
18 18 5 48052 133663 K909_GB10 0
19 31 3 48106 133686 Network Data Services 1
20 22 4 133667 48106 Total DTO Internet 1
21 21 5 48056 133667 K909_GD01 0
22 22 5 48057 133667 K909_GD07 0
23 24 4 133670 48106 MetroNet 1
24 24 5 48058 133670 K909_GD02 0
25 26 4 133673 48106 Router Management 1
26 26 5 48060 133673 K909_GD03 0
27 29 4 133676 48106 Firll / VN Management 1
28 28 5 48061 133676 K909_GD04 0
29 29 5 48062 133676 K909_GD06 0
30 31 4 48103 48106 Total WAN 1
31 31 5 48066 48103 K909_GD05 0
32 57 2 133689 48039 Shared Services 1
33 45 3 133634 133689 Server Hosting Services 1
34 34 4 133638 133634 K909_GG48 0
35 35 4 133679 133634 K909_GG49 0
36 36 4 133680 133634 K909_GG07 0
37 37 4 138164 133634 K909_GG04 0
38 38 4 48076 133634 K909_GG40 0
39 39 4 48077 133634 K909_GG41 0
40 40 4 48078 133634 K909_GG42 0
41 41 4 48079 133634 K909_GG43 0
42 42 4 48080 133634 K909_GG44 0
43 43 4 48081 133634 K909_GG45 0
44 44 4 48082 133634 K909_GG46 0
45 45 4 48083 133634 K909_GG47 0
46 51 3 133640 133689 Desktop Support & Application Services 1
47 48 4 133642 133640 Application Services 1
48 48 5 48074 133642 K909_GG30 0
49 51 4 133644 133640 Total Desktop Support 1
50 50 5 133678 133644 K909_GG21 0
51 51 5 48072 133644 K909_GG20 0
52 57 3 48105 133689 Mainframe Services 1
53 53 4 48067 48105 K909_GG11 0
54 54 4 48068 48105 K909_GG12 0
55 55 4 48069 48105 K909_GG13 0
56 56 4 48070 48105 K909_GG14 0
57 57 4 48071 48105 K909_GG10 0
58 62 2 133692 48039 SW Security K-12 Monitoring 1
59 62 3 133682 133692 K12 Monitoring 1
60 60 4 133681 133682 K909_GM00 0
61 61 4 133683 133682 K909_GM01 0
62 62 4 133684 133682 K909XGM01 0
63 66 2 133695 48039 Print Services Group 1
64 64 3 140641 133695 K909_GZ00 0
65 66 3 48108 133695 Total Print Services 1
66 66 4 48095 48108 K909_GK00 0
67 73 2 138170 48039 Miscellaneous Service Groups 1
68 68 3 138162 138170 K909XGA01 0
69 69 3 138163 138170 Surplus 0
70 70 3 138165 138170 Help Desk 0
71 71 3 138166 138170 DS 0
72 72 3 138167 138170 SCS 0
73 73 3 138168 138170 IT Plan and Admin 0
74 78 2 48100 48039 Ovhd Service Groups 1
75 75 3 140631 48100 K909_GG00 0
76 76 3 140634 48100 K909_GA02 0
77 77 3 140656 48100 K909_GB00 0
78 78 3 48045 48100 G0000002 0
79 85 2 48109 48039 Total Shared Services Security 1
80 80 3 48089 48109 GH010001 0
81 81 3 48090 48109 GH010002 0
82 82 3 48091 48109 GH010003 0
83 83 3 48092 48109 GH010004 0
84 84 3 48093 48109 GH010005 0
85 85 3 48094 48109 GH010006 0
(85 row(s) affected)][1]][1]
现在，这是很酷的部分！
Now, this is the cool part!
** 我在表Co1_Trans
** I created some random data at the NON-PARENT or leaf level in table Co1_Trans
以下查询说明了如何通过范围键汇总数据
The following query illustrates how to roll the data up via the range keys
Select H.R1
,H.R2
,H.Lvl
,H.ID
,H.ParentID
,H.CID
,H.IsParent
,NestedDesc = space((H.Lvl-1)*2)+H.CID
,Records = count(*)
,MinVal = min(SomeValue)
,MaxVal = max(SomeValue)
,AvgVal = Avg(SomeValue)
,SomeValue = sum(SomeValue)
,SomeOtherValue = sum(SomeOtherValue)
From dbo.CO1_Hier H
Join (Select _R1=B.R1,A.* From [dbo].[CO1_Trans] A Join dbo.CO1_Hier B on (A.ID=B.ID)) B
on (B._R1 between H.R1 and H.R2)
Group By H.R1
,H.R2
,H.Lvl
,H.ID
,H.ParentID
,H.CID
,H.IsParent
Order By H.R1
结果显示在这里
The results are displayed here
现在，假设您只想显示级别1和2.让我们使用与以前相同的查询，但是添加一个where子句.. W.Hvl <3的地方
Now, let's say that you only wanted to show Levels 1 and 2. Let's use the same query as before, but add a where clause .. Where H.Lvl<3
...
,SomeOtherValue = sum(SomeOtherValue)
From dbo.CO1_Hier H
Join (Select _R1=B.R1,A.* From [dbo].[CO1_Trans] A Join dbo.CO1_Hier B on (A.ID=B.ID)) B on (B._R1 between H.R1 and H.R2)
Where H.Lvl<3
Group By H.R1
,H.R2
,H.Lvl
...
这篇关于具有父ID和子ID的SQL Server层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
