Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
ìm kiếm thông xuống hệ thống phân cấp với con đường hiện thực Điều hướng xuống hệ thống phân cấp và trả một cây con của tất cả các nút theo một nút cho trước là nơi mà các phương pháp vật hoá con đường thực sự tỏa sáng. Kiểm tra đơn giản của truy vấn này: | Part III Beyond Relational ON C.BusinessEntityID Emp.BusinessEntityID LEFT JOIN Person.Person AS M ON Emp.ManagerlD M.BusinessEntityID ORDER BY Lv BusinessEntityID Result abbreviated BusinessEntityID ManagerID Lv Name JobTitle Manager 1 263 Jean Trenary Information Services Manager 1 Ken Sanchez 2 264 Stephanie Conroy Network Manager 263 Jean Trenary 2 267 Karen Berg Application Specialist 263 Jean Trenary 2 268 Ramesh Meyyappan Application Specialist 263 Jean Trenary 2 269 Dan Bacon Application Specialist 263 Jean Trenary 2 270 Francois Ajenstat Database Administrator 263 Jean Trenary 2 271 Dan Wilson Database Administrator 263 Jean Trenary 2 272 Janaina Bueno Application Specialist 263 Jean Trenary 3 265 Ashvini Sharma Network Administrator 264 Stephanie Conroy 3 266 Peter Connelly Network Administrator 264 Stephanie Conroy A nice feature of the table-valued user-defined function is that it can be called from the CROSS APPLY new in SQL Server 2005 which executes the function once for every row in the outer query. Here the CROSS APPLY is used with the function to generate an extensive list of every report under every BusinessEntityID from HumanResources.Employee -- using Cross Apply to report all node under everyone SELECT E.BusinessEntitylD OT.BusinessEntitylD OT.Lv FROM HumanResources.Employee AS E CROSS APPLY dbo.OrgTree BusinessEntityID AS OT The next query builds on the previous query adding a GROUP BY to present a count of the number of reports under every manager. Because it explodes out every manager with its complete subtree this query returns not 290 rows but 1 308 -- Count of All Reports SELECT E.BusinessEntitylD COUNT OT.BusinessEntityID -1 AS ReportCount FROM HumanResources.Employee E CROSS APPLY dbo.OrgTree BusinessEntitylD OT GROUP BY E.BusinessEntityID HAVING COUNT OT.BusinessEntitylD 1 ORDER BY COUNT OT.BusinessEntitylD DESC Result abbreviated BusinessEntityID BusinessEntityID Lv 1 1 1 1 2 2 1 16 2 1 25 2 1 234 2 412 www.getcoolebook.com .
