我们通过CTE的方式来查询父以及全部的下级,则查询出来的是多行数据

只访问根的路径由单条斜杠表示,这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列,则查询出来的是多行数据,而借助于,Name,SELECT OBJECT,WHERE id IN(SELECT object

图片 3

2.1 TIP

依附3个知识点能够做到这一个手续

 

select * INTO #temp from (SELECT ‘1-2-3’运单号,’a’ 小票的数量码卡塔尔t;
WITH cte AS (select
收据号码,SUBST奥迪Q3ING(运单号,1,CHA奥迪Q3INDEX(‘-‘,运单号+’-‘卡塔尔-1卡塔尔(قطر‎ as
运单号,CHAMuranoINDEX(‘-‘,运单号+’-‘卡塔尔 as station from #temp
union all
select
a.收据号码,SUBSTPAJEROING(a.运单号,b.station+1,CHARAV4INDEX(‘-‘,a.运单号+’-‘,b.station+1State of Qatar-b.station-1State of Qatar,CHA宝马7系INDEX(‘-‘,a.运单号+’-‘,b.station+1卡塔尔国
FROM #temp AS a join cte AS b on a.收据号码=b.小票号码
where CHARINDEX(‘-‘,a.运单号+’-‘,b.station+1)>0)
select 发票号码,运单号 from cte
DROP TABLE #temp

• /1/3/ 

create function f_cidname(@id nvarchar(50)) returns nvarchar(max)
as
begin
    declare @cids nvarchar(max);
    set @cids=”;
    declare @pNames nvarchar(max);
    set @pNames=”;
    with cte as
    ( select id,parentid,name from EL_QuestionBank.QuestionCategory
where id=@id
    union all
    select b.id,b.parentid,b.name from cte A
,EL_QuestionBank.QuestionCategory B where a.id = b.parentid )
   
    select TOP 100 @cids=@cids + id + ‘,’, @pNames=@pNames + name +
‘->’ from cte
    return @cids + ‘###’ + @pNames
end
go
select top 100 *, dbo.f_cidname(id) from
EL_QuestionBank.QuestionCategory A where id=’1′
drop function f_cidname

图片 1图片 2代码

while(charindex(@split,@c)<>0)
begin
insert @t(colval) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),”)
end
insert @t(colval) values ( @c)
return
END

 

二:多行产生生机勃勃行

  1 create table Employee
  2 (
  3 Id INT IDENTITY(1,1) PRIMARY KEY, 
  4 [Name] varchar(30) null, 
  5 JobTitle varchar(30) null, 
  6 Manager int null
  7 )
  8 
  9 insert Employee
 10 select ‘incf’,     ‘IT Director’,null union all
 11 select ‘inc3’,     ‘Finance Director’,null union all
 12 select ‘geovindu’,     ‘assces’,1 union all
 13 select ‘du’,     ‘assces’,1 union all
 14 select ‘fa’,’account’,2 union all
 15 select ‘d’,’account’,2  
 16 
 17 WITH OrganisationChart (Id, [Name], JobTitle, Manager) AS
 18 (
 19       SELECT
 20             Id, [Name], JobTitle, Manager
 21       FROM dbo.Employee
 22       WHERE
 23             Manager IS NULL
 24       UNION ALL
 25       SELECT emp.Id, emp.[Name], emp.JobTitle, emp.Manager
 26       FROM dbo.Employee emp
 27       INNER JOIN OrganisationChart ON
 28             emp.Manager = OrganisationChart.Id
 29 )
 30 SELECT * FROM OrganisationChart
 31 
 32 
 33 WITH OrganisationChart (Id,  [Name], JobTitle, [Level], Manager) AS
 34 (
 35       SELECT
 36             Id, [Name], JobTitle, 0, Manager
 37       FROM  dbo.Employee
 38       WHERE
 39            Manager IS NULL
 40       UNION ALL
 41       SELECT emp.Id, emp.[Name], emp.JobTitle, [Level] + 1, emp.Manager
 42       FROM dbo.Employee emp
 43       INNER JOIN OrganisationChart ON
 44             emp.Manager = OrganisationChart.Id
 45 )
 46 SELECT * FROM OrganisationChart
 47 ORDER BY [Level]
 48 
 49 
 50 WITH OrganisationChart (Id, [Name], JobTitle, [Level], Manager, [Root]) AS
 51 (
 52       SELECT  Id, [Name], JobTitle, 0, Manager, Id
 53       FROM  dbo.Employee
 54       WHERE  Manager IS NULL
 55       UNION ALL
 56       SELECT  emp.Id,emp.[Name],emp.JobTitle,[Level] + 1,emp.Manager,[Root]
 57       FROM dbo.Employee emp
 58       INNER JOIN OrganisationChart ON
 59             emp.Manager = OrganisationChart.Id
 60 )
 61 SELECT * FROM OrganisationChart
 62 –WHERE [Name] = ‘incf’
 63 WHERE [Root] = 1
 64 
 65  
 66 
 67 –示例数据库
 68 
 69 /*
 70 递归查询对于同叁个表父亲和儿子关系的忖度提供了比非常大的方便,那些示例使用了SQL server 贰零零陆中的递归查询,使用的表是CarParts,那么些表存款和储蓄了豆蔻年华辆小车的兼具零器件以至组织,part为零件单位,subpart为子零部件,Qty为数量。
 71 
 72 具体示比如下:
 73 
 74 */ 
 75 CREATE TABLE CarParts
 76 
 77 (
 78 
 79 CarID INT NOT NULL,
 80 
 81 Part VARCHAR(15),
 82 
 83 SubPart VARCHAR(15),
 84 
 85 Qty INT
 86 
 87 )
 88 
 89 GO
 90 
 91 INSERT CarParts VALUES (1, ‘Body’, ‘Door’, 4)
 92 
 93 INSERT CarParts VALUES (1, ‘Body’, ‘Trunk Lid’, 1)
 94 
 95 INSERT CarParts VALUES (1, ‘Body’, ‘Car Hood’, 1)
 96 
 97 INSERT CarParts VALUES (1, ‘Door’, ‘Handle’, 1)
 98 
 99 INSERT CarParts VALUES (1, ‘Door’, ‘Lock’, 1)
100 
101 INSERT CarParts VALUES (1, ‘Door’, ‘Window’, 1)
102 
103 INSERT CarParts VALUES (1, ‘Body’, ‘Rivets’, 1000)
104 
105 INSERT CarParts VALUES (1, ‘Door’, ‘Rivets’, 100)
106 
107 INSERT CarParts VALUES (1, ‘Door’, ‘Mirror’, 1)
108 
109 INSERT CarParts VALUES (1, ‘Mirror’, ‘small_Mirror’, 4)
110 
111 GO
112 
113 SELECT * FROM CarParts
114 
115 GO
116 
117 /*
118 
119 意气风发辆汽车须要各种零器件的数据
120 
121 1个Body 需要4个Door
122 
123 1个Door 需要1个Mirror
124 
125 那么
126 
127 1个body需要4个Mirror
128 
129 构造很简单吗
130 
131 */
132 
133 WITH CarPartsCTE(SubPart, Qty)
134 
135 AS
136 
137 (
138 
139 — 长久成员 (AM卡塔尔国:
140 
141 — SELECT查询没有必要参谋CarPartsCTE
142 
143 — 递归从这里初步
144 
145 SELECT SubPart, Qty
146 
147 FROM CarParts
148 
149 WHERE Part = ‘Body’
150 
151 UNION ALL
152 
153 — 递归成员 (RMState of Qatar:
154 
155 — SELECT查询参考CarPartsCTE
156 
157 — 使用现存数据往下生机勃勃层进行
158 
159 SELECT CarParts.SubPart, CarPartsCTE.Qty * CarParts.Qty
160 
161 FROM CarPartsCTE
162 
163 INNER JOIN CarParts ON CarPartsCTE.SubPart = CarParts.Part
164 
165 WHERE CarParts.CarID = 1
166 
167 )
168 
169 SELECT SubPart,Qty AS TotalNUM
170 
171 FROM CarPartsCTE
172 
173 /*
174 
175 注意看最下层的small_Mirror 坐落于 表最终的职位,
176 
177 由此可以看见改递归不是发端就进展递归查询而是在1层完全展开后在依据该层张开下风流洒脱层不是深度优先的递归
178 
179 */
180 
181 drop table CarParts 
182 
183  
184 
185 WITH DirectReports(groupid, member, EmployeeLevel,type) AS
186 
187 (
188 
189 SELECT groupid, member, 0,type AS EmployeeLevel
190 
191 FROM groupinfo
192 
193 WHERE groupid = ‘finance_company’
194 
195 UNION ALL
196 
197 SELECT e.groupid, e.member, EmployeeLevel + 1,e.type
198 
199 FROM groupinfo e
200 
201 INNER JOIN DirectReports d
202 
203 ON e.groupid = d.member
204 
205 )
206 
207 SELECT b.nickname,groupid, member, EmployeeLevel,type
208 
209 FROM DirectReports,userbasicinfo b
210 
211 where DirectReports.member=b.id
212 
213 and type = 1 
214 
215 
216 USE AdventureWorks;
217 GO
218 WITH DirReps(ManagerID, DirectReports) AS 
219 (
220     SELECT ManagerID, COUNT(*) 
221     FROM HumanResources.Employee AS e
222     WHERE ManagerID IS NOT NULL
223     GROUP BY ManagerID
224 )
225 SELECT ManagerID, DirectReports 
226 FROM DirReps 
227 ORDER BY ManagerID;
228 GO
229 
230 
231 WITH DirReps (Manager, DirectReports) AS 
232 (
233     SELECT ManagerID, COUNT(*) AS DirectReports
234     FROM HumanResources.Employee
235     GROUP BY ManagerID
236 ) 
237 SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
238 FROM DirReps 
239 WHERE DirectReports>= 2 ;
240 GO
241 
242  
243 
244 
245 USE AdventureWorks;
246 GO
247 WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
248 AS
249 (
250     SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
251     FROM Sales.SalesOrderHeader
252     GROUP BY SalesPersonID
253 )
254 SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
255     E.ManagerID, OM.NumberOfOrders, OM.MaxDate
256 FROM HumanResources.Employee AS E
257     JOIN Sales_CTE AS OS
258     ON E.EmployeeID = OS.SalesPersonID
259     LEFT OUTER JOIN Sales_CTE AS OM
260     ON E.ManagerID = OM.SalesPersonID
261 ORDER BY E.EmployeeID;
262 GO
263 
264 —管理員下的下屬
265 USE AdventureWorks;
266 GO
267 WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
268 (
269     SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
270     FROM HumanResources.Employee
271     WHERE ManagerID IS NULL
272     UNION ALL
273     SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
274     FROM HumanResources.Employee e
275         INNER JOIN DirectReports d
276         ON e.ManagerID = d.EmployeeID 
277 )
278 SELECT ManagerID, EmployeeID, EmployeeLevel 
279 FROM DirectReports 
280 WHERE EmployeeLevel <= 2 ;
281 GO
282 
283 USE AdventureWorks;
284 GO
285 WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
286 AS (SELECT CONVERT(varchar(255), c.FirstName + ‘ ‘ + c.LastName),
287         e.Title,
288         e.EmployeeID,
289         1,
290         CONVERT(varchar(255), c.FirstName + ‘ ‘ + c.LastName)
291     FROM HumanResources.Employee AS e
292     JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
293     WHERE e.ManagerID IS NULL
294     UNION ALL
295     SELECT CONVERT(varchar(255), REPLICATE (‘| ‘ , EmployeeLevel) +
296         c.FirstName + ‘ ‘ + c.LastName),
297         e.Title,
298         e.EmployeeID,
299         EmployeeLevel + 1,
300         CONVERT (varchar(255), RTRIM(Sort) + ‘| ‘ + FirstName + ‘ ‘ + 
301                  LastName)
302     FROM HumanResources.Employee as e
303     JOIN Person.Contact AS c ON e.ContactID = c.ContactID
304     JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
305     )
306 SELECT EmployeeID, Name, Title, EmployeeLevel
307 FROM DirectReports 
308 ORDER BY Sort;
309 GO
310 
311 USE AdventureWorks;
312 GO
313 –Creates an infinite loop
314 WITH cte (EmployeeID, ManagerID, Title) as
315 (
316     SELECT EmployeeID, ManagerID, Title
317     FROM HumanResources.Employee
318     WHERE ManagerID IS NOT NULL
319   UNION ALL
320     SELECT cte.EmployeeID, cte.ManagerID, cte.Title
321     FROM cte 
322     JOIN  HumanResources.Employee AS e 
323         ON cte.ManagerID = e.EmployeeID
324 )
325 –Uses MAXRECURSION to limit the recursive levels to 2
326 SELECT EmployeeID, ManagerID, Title
327 FROM cte
328 OPTION (MAXRECURSION 2);
329 GO
330 
331  
332 
333 USE AdventureWorks;
334 GO
335 WITH cte (EmployeeID, ManagerID, Title)
336 AS
337 (
338     SELECT EmployeeID, ManagerID, Title
339     FROM HumanResources.Employee
340     WHERE ManagerID IS NOT NULL
341   UNION ALL
342     SELECT  e.EmployeeID, e.ManagerID, e.Title
343     FROM HumanResources.Employee AS e
344     JOIN cte ON e.ManagerID = cte.EmployeeID
345 )
346 SELECT EmployeeID, ManagerID, Title
347 FROM cte;
348 GO
349 
350 
351 USE AdventureWorks;
352 GO
353 WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
354 (
355     SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
356         b.EndDate, 0 AS ComponentLevel
357     FROM Production.BillOfMaterials AS b
358     WHERE b.ProductAssemblyID = 800
359           AND b.EndDate IS NULL
360     UNION ALL
361     SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
362         bom.EndDate, ComponentLevel + 1
363     FROM Production.BillOfMaterials AS bom 
364         INNER JOIN Parts AS p
365         ON bom.ProductAssemblyID = p.ComponentID
366         AND bom.EndDate IS NULL
367 )
368 SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
369         ComponentLevel 
370 FROM Parts AS p
371     INNER JOIN Production.Product AS pr
372     ON p.ComponentID = pr.ProductID
373 ORDER BY ComponentLevel, AssemblyID, ComponentID;
374 GO
375 

–查看被锁表:
select request_session_id
spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type=’OBJECT’

DECLARE @tmpIds hierarchyid
SELECT @tmpIds=Pids FROM EL_Organization.Organization WHERE
ID=’ecc43c7159924dca91e2916368f923f4′;
WITH CTEGetChild AS  (
    SELECT * FROM EL_Organization.Organization WHERE
ID=’ecc43c7159924dca91e2916368f923f4′
    UNION ALL(
    SELECT * FROM EL_Organization.Organization WHERE
Pids.IsDescendantOf(@tmpIds)=1
    )
)
SELECT * FROM CTEGetChild

create function f_pidname(@id nvarchar(50)) returns nvarchar(max)
as
begin
    declare @pids nvarchar(max);
    declare @pNames nvarchar(max);
    set @pids=”;
    set @pNames=”;
    with cte as
    ( select id,parentid,name from EL_QuestionBank.QuestionCategory
where id=@id
    union all
    select b.id,b.parentid,b.name from cte A
,EL_QuestionBank.QuestionCategory B where a.parentid = b.id )
    select @pids=@pids + id + ‘,’, @pNames=@pNames + name + ‘->’
from cte
    return @pids + ‘###’ + @pNames
end
go
select top 100 *, dbo.f_pidname(parentid) from
EL_QuestionBank.QuestionCategory A
drop function f_pidname

print ‘inputbuffer for running processes’
declare @spid varchar(6)
declare ibuffer cursor fast_forward for
select cast(spid as varchar(6)) as spid from sysprocesses where spid=156
or spid=177
or spid=214 or spid=206
open ibuffer
fetch next from ibuffer into @spid
while (@@FETCH_STATUS!=-1)
begin
print ”
print ‘DBCC inputbuffer for spid ‘+@spid
exec (‘dbcc inputbuffer(‘+@spid+’)’)
fetch next from ibuffer into @spid
end
deallocate ibuffer
waitfor delay ‘0:0:10’
end

这种门路在逻辑上代表为叁个在根之后被访谈的兼具子级的节点标签连串。
表示方式以一条斜杠从前,只访谈根的路线由单条斜杠表示。
对于根以下的各级,各标签编码为由点分隔的整数系列。
子级之间的可比正是按字典顺序相比由点分隔的整数连串。
每一种等级前面紧跟着贰个斜杠。 因而斜杠将父级与其子级分隔断。
例如,以下是长度分别为 1 级、2 级、2 级、3 级和 3 级的灵光 hierarchyid
路线:

图片 3

CREATE FUNCTION [dbo].[f_Split](@c NVARCHAR(max),@split
NVARCHAR(2))
returns @t table(colval NVARCHAR(max))
as
begin

注意,id是guid的三九个人字符串,而hierarchyid字段不扶持那么大的Path内路线,于是我们将GUID转为了整型:convert(varchar(32卡塔尔国,Convert(int,
Convert(varbinary(max卡塔尔(قطر‎, id卡塔尔国State of Qatar卡塔尔国 

事实上,上边的 with 字句,若是利用

–spid 锁表进度
–tableName 被锁表名

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

校勘一下地点的讲话,创制自定义函数,雷同为:

GO

• / 

 

–查询库全体表名
SELECT NAME from sysobjects where xtype=’u’

UPDATE EL_Organization.Organization SET PIDS=dbo.f_cidname(id)

三:使用自定义函数

 –根据分隔符一条变多条