程式筆記

Query：

案例:查詢第一階層的資料，看誰要report給ManagerA和ManagerA的老闆是誰

SELECT 
E.[ID],E.[Name],E.[ParentId],E1.[ID],E1.[Name],E1.[ParentId]
FROM 
    myEmpNode e, myEmpNode e1, myEmpReportsTo m 
WHERE 
    MATCH(e-(m)->e1)
and e.[Name]='ManagerA';

ID	Name	ParentId	ID	Name	ParentId
2	managerA	1	5	empA	2
2	managerA	1	6	empB	2

案例:查詢第二層資料

SELECT 
E.[ID],E.[Name],E.[ParentId],E1.[ID],E1.[Name],E1.[ParentId],E2.[ID],e2.[Name],E2.[ParentId]
FROM 
    myEmpNode e, myEmpNode e1, myEmpReportsTo m ,myEmpReportsTo m1, myEmpNode e2
WHERE 
    MATCH(e-(m)->e1-(m1)->e2)
and e.[Name]='ManagerA';

ID	Name	ParentId	ID	Name	ParentId	ID	Name	ParentId
2	managerA	1	6	empB	2	8	empD	6
2	managerA	1	6	empB	2	9	empE	6

Delete：

案例：解除EmpC與ManagerB的主管部屬關係(myEmpReportsTo)

方法1：用$edge_id刪除

找出關係(myEmpReportsTo)的$edge_id

select * from myEmpReportsTo;

DELETE myEmpReportsTo WHERE $edge_id ='{"type":"edge","schema":"dbo","table":"myEmpReportsTo","id":7}';

方法2：用內建含數(System Functions)

DECLARE @obj INT = OBJECT_ID('dbo.myEmpReportsTo');
DELETE myEmpReportsTo WHERE $edge_id = EDGE_ID_FROM_PARTS(@obj, 7);

Update：

案例：因EmpB離職, 一併解除與EmpD和EmpE的部屬關係；並將D/E改掛到A下；

Step0：直接更新empD的主管為empA-->會失敗, 無法直接更新$node_id/$from_id/$to_id/$edge_id

UPDATE myEmpReportsTo
SET $from_id = (SELECT $node_id FROM myEmpNode WHERE ID = 5)
WHERE GRAPH_ID_FROM_NODE_ID($to_id) = 7;

訊息 271，層級 16，狀態 1，行 8
The column "$from_id_9931E5F4CF464AD3826ACEA80425B631" cannot be modified because it is either a computed column or is the result of a UNION operator.

Step1：移除B與D/E的關係

DELETE myEmpReportsTo
FROM myEmpNode empH, myEmpNode empL, myEmpReportsTo rpt
WHERE MATCH(empH-(rpt)->empL)
    AND empH.[Name] = 'EmpB';

Step2：刪除EmpB資料

--找出node_id
select * from myEmpNode where Name='empB';

$node_id_7C1EDEE8A57C47BAACAE74B1B68A5CA1	ID	Name	ParentId	Path
{"type":"node","schema":"dbo","table":"myEmpNode","id":5}	6	empB	2	/1/2/

DECLARE @obj INT = OBJECT_ID('dbo.myEmpNode');
DELETE myEmpNode WHERE $node_id = NODE_ID_FROM_PARTS(@obj, 5);

Step3：新增empD/empE與empA的部屬關係

方法一：

INSERT INTO myEmpReportsTo ($from_id, $to_id, [Path]) VALUES 
    ((SELECT $node_id FROM myEmpNode WHERE ID = 5), (SELECT $node_id FROM myEmpNode WHERE ID = 8),'/1/1/'),
    ((SELECT $node_id FROM myEmpNode WHERE ID = 5), (SELECT $node_id FROM myEmpNode WHERE ID = 9),'/1/1/');

方法二：

DECLARE @obj1 INT = OBJECT_ID('dbo.myEmpNode');
INSERT INTO myEmpReportsTo ($from_id, $to_id,[Path]) VALUES 
(NODE_ID_FROM_PARTS(@obj1, 4), NODE_ID_FROM_PARTS(@obj1, 7),'/1/1/'),
(NODE_ID_FROM_PARTS(@obj1, 4), NODE_ID_FROM_PARTS(@obj1, 8),'/1/1/');

Step4：查詢empD/empE的報告主管

SELECT 
E.[ID],E.[Name],E.[ParentId],E1.[ID],E1.[Name],E1.[ParentId]
FROM 
    myEmpNode e, myEmpNode e1, myEmpReportsTo m 
WHERE 
    MATCH(e-(m)->e1)
and e1.[Name] in ('empD','empE');

ID	Name	ParentId	ID	Name	ParentId
5	empA	2	8	empD	6
5	empA	2	9	empE	6

測試資料建置SQL：

drop table myEmp;

CREATE TABLE dbo.myEmp(
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ParentId] [int],
[Path] nvarchar(50)
);

INSERT INTO myEmp values ('1','boss',null,'/');
INSERT INTO myEmp values ('2','managerA','1','/1/');
INSERT INTO myEmp values ('3','managerB','1','/2/');
INSERT INTO myEmp values ('4','managerC','1','/3/');
INSERT INTO myEmp values ('5','empA','2','/1/1/');
INSERT INTO myEmp values ('6','empB','2','/1/2/');
INSERT INTO myEmp values ('7','empC','3','/2/3/');
INSERT INTO myEmp values ('8','empD','6','/1/1/2/');
INSERT INTO myEmp values ('9','empE','6','/1/1/3/');

drop table dbo.myEmpNode;
--定義Node table
CREATE TABLE dbo.myEmpNode(
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[ParentId] [int],
[Path] [varchar](50),
) AS NODE;

INSERT INTO myEmpNode([ID],[Name],[ParentId],[Path]) select [ID],[Name],[ParentId],[Path] from myEmp;
--select * from myEmpNode;


drop table myEmpReportsTo;
--定義Edge table
CREATE TABLE myEmpReportsTo(Path varchar(100)) AS EDGE;

--使用ID和parentID定義關聯性
INSERT INTO myEmpReportsTo 
SELECT e.$node_id, m.$node_id,e.Path
FROM dbo.myEmpNode e 
inner JOIN dbo.myEmpNode m 
ON e.[ID] = m.[ParentId];  

select * from myEmpReportsTo;