By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T(
EmpNum NVARCHAR(50),
EmpName NVARCHAR(50),
Job NVARCHAR(50),
SNum NVARCHAR(50)
);
INSERT INTO T VALUES ('A01',N'老總',N'總經理','0');
INSERT INTO T VALUES ('B23',N'陳一哥',N'經理','A01');
INSERT INTO T VALUES ('B666',N'蘇老大',N'組長','B23');
INSERT INTO T VALUES ('C666',N'燈芸姊',N'全端工程師','B666');
INSERT INTO T VALUES ('C52',N'大搖哥',N'全能工程師','B666');
WITH EmployeeOrder AS (
--找出老大
SELECT EmpNum, EmpName
, Job
, SNum
, 1 AS JobLevel
FROM T
WHERE SNum = '0'
UNION ALL
SELECT A.EmpNum, A.EmpName
, A.Job, A.SNum
, (B.JobLevel + 1) AS JobLevel --職位等級+1
FROM T A
INNER JOIN EmployeeOrder B ON A.SNum = B.EmpNum
)
SELECT * FROM EmployeeOrder
| EmpNum | EmpName | Job | SNum | JobLevel |
|---|---|---|---|---|
| A01 | 老總 | 總經理 | 0 | 1 |
| B23 | 陳一哥 | 經理 | A01 | 2 |
| B666 | 蘇老大 | 組長 | B23 | 3 |
| C666 | 燈芸姊 | 全端工程師 | B666 | 4 |
| C52 | 大搖哥 | 全能工程師 | B666 | 4 |