資料庫黑洞

有的時候，我們會遇到有多個階層關係的資料，放在同一張表裡，例如：員工資料表，但是表中並沒有階層的序號，這時候使用 CTE 來做遞迴，會變得比較容易看出階層。

 

 

-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-02
-- Update date: 2014-06-02
-- Description:	建立員工資料表並寫入資料
-- Require: none
-- =============================================
--存在則刪除
IF OBJECT_ID('Employee', 'U') IS NOT NULL
	DROP TABLE Employee;
GO

--建立員工資料表
CREATE TABLE Employee
(
	EmployeeNum VARCHAR(20) NOT NULL,
	EmployeeName NVARCHAR(20) NOT NULL,
	Job NVARCHAR(20) NOT NULL,
	SuperiorNum VARCHAR(20) NOT NULL
);

--寫入員工資料
INSERT INTO Employee (EmployeeNum, EmployeeName, Job, SuperiorNum)
VALUES
	('A01', '老總', '總經理', '0'),
	('B23', '陳一哥', '經理', 'A01'),
	('B666', '蘇老大', '組長', 'B23'),
	('C666', '燈芸姊', '全端工程師', 'B666'),
	('C52', '大搖哥', '全能工程師', 'B666');

--查詢
SELECT * FROM Employee;

 

SuperiorNum 表示上級主管的員工編號，只有總經理是 0，其他都有對應到上級主管，整個表的階層關係不是很明顯

 

使用範例：

-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-01
-- Update date: 2014-06-01
-- Description:	使用 CTE 做遞迴
-- Require: none
-- =============================================
;WITH EmployeeOrder AS (
	--找出老大
	SELECT EmployeeNum, EmployeeName, Job, SuperiorNum, 1 AS JobLevel
	FROM Employee WHERE SuperiorNum = '0'
	UNION ALL
	--跟 CTE 自身做遞迴 JOIN (A 的上級主管[SuperiorNum]是 EmployeeOrder 的員工編號[EmployeeNum])
	SELECT A.EmployeeNum, A.EmployeeName, A.Job, A.SuperiorNum
		, (B.JobLevel + 1) AS JobLevel --職位等級+1
	FROM Employee A
	INNER JOIN EmployeeOrder B ON A.SuperiorNum = B.EmployeeNum
)
SELECT * FROM EmployeeOrder

 

 

參考資料：

WITH common_table_expression (Transact-SQL)

資料操作技巧: 一般資料表運算式

 

 

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~