完整的任務聽起來像這樣：顯示直接上級較年輕且在公司雇用較少的員工的內容。
列：
Manager Name | Date of hiring a manager | Head's date of birth
Employee name | Employee hiring date | Employee's date of birth
我已經在這里打破了我的頭：
SELECT
LastName ' ' FirstName AS SupervisorFullName,
HireDate,
BirthDate,
(SELECT LastName ' ' FirstName
FROM HumanResources.Employee AS subHrE
WHERE HrE.OrganizationNode.IsDescendantOf(subHrE.OrganizationNode) = 1
AND HrE.OrganizationLevel = HrE.OrganizationNode.GetLevel() 1) AS EmployeeFullName,
(SELECT HireDate
FROM HumanResources.Employee AS subHrE
WHERE HrE.OrganizationNode.IsDescendantOf(subHrE.OrganizationNode) = 1
AND HrE.OrganizationLevel = HrE.OrganizationNode.GetLevel() 1) AS HireDateEmp,
(SELECT BirthDate
FROM HumanResources.Employee AS subHrE
WHERE HrE.OrganizationNode.IsDescendantOf(subHrE.OrganizationNode) = 1
AND HrE.OrganizationLevel = HrE.OrganizationNode.GetLevel() 1) AS BithDateEmp
FROM
HumanResources.Employee as HrE
JOIN
Person.Person as P ON HrE.BusinessEntityID = P.BusinessEntityID
ORDER BY
SupervisorFullName ASC
輸出
AdventureWork2016 db 用于作業
完整架構 AW2016
uj5u.com熱心網友回復：
There's definitely a certain way to think about use of hierarchyid that, once you get the hang of it, opens a lot of possibilities. Here's what I came up with:
WITH FullPerson AS (
SELECT CONCAT_WS(' ', p.FirstName, p.MiddleName, p.LastName) AS [FullName],
e.HireDate,
e.BirthDate,
e.OrganizationNode
FROM HumanResources.Employee AS e
JOIN Person.Person AS p
ON p.BusinessEntityID = e.BusinessEntityID
)
SELECT
manager.OrganizationNode.ToString(),
manager.FullName,
manager.HireDate,
manager.BirthDate,
subordinate.OrganizationNode.ToString(),
subordinate.FullName,
subordinate.HireDate,
subordinate.BirthDate
FROM FullPerson AS subordinate
JOIN FullPerson AS manager
ON subordinate.OrganizationNode.GetAncestor(1) = manager.OrganizationNode
WHERE manager.HireDate > subordinate.HireDate
AND manager.BirthDate > subordinate.BirthDate;
Breaking it down, I create a common table expression to join Employee and Person as I'll need columns from both tables for both subordinates and their managers. The real trick is the join condition. subordinate.OrganizationNode.GetAncestor(1) = manager.OrganizationNode says "take the subordinate's OrganizationNode and go one level up the tree". What's amazing to me is that this sort of query can be supported by indexes and indeed there is an index on that column in the AdventureWorks schema! In addition to the columns you asked for, I added a human-readable representation of OrganizationNode to help with the visualization of how the data relates.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/355177.html
標籤：sql sql-server 查询语句
上一篇：SQL-每小時選擇最高數量類別