如果該表只有兩列包含員工姓名和他們的主管列，并且沒有任何其他帶有員工編號或員工 ID 的數字或數字列，那么如何生成結果。我沒有邏輯來顯示結果。
Mysql建表代碼：
CREATE TABLE DATABASE_TABLE
(
Employee_Name nvarchar(255) PRIMARY KEY,
Supervisor_Name nvarchar(255) NOT NULL
);
CREATE INDEX ix_database_table_supervisor
ON DATABASE_TABLE (Supervisor_Name);
INSERT INTO DATABASE_TABLE
(Employee_Name, Supervisor_Name) VALUES
('Alice','Dave'), ('Olive','Dave'), ('Barton','Dave')
, ('Almira','Jacob'), ('Charles','Jacob'), ('Davis','Jacob')
, ('Robert','Risha'), ('Peter','Risha'), ('Ethel','Risha')
, ('Isaac','Jospeh'), ('Sophia','Jospeh'), ('Rosa','Jospeh')
, ('Joshua','Dandy'), ('Silas','Dandy'), ('Fred','Dandy')
, ('Frank','Andrew'), ('Howard','Andrew'), ('Ralph','Andrew')
, ('Dennis','Henry'), ('Alex','Henry'), ('Floyd','Henry')
, ('Carlos','Nelson'), ('Homer','Nelson'), ('Harold','Nelson')
, ('Leo','Simon'), ('Warren','Simon'), ('Clifford','Simon')
, ('Martha','Casper'), ('Hazel','Casper'), ('Irene','Casper')
, ('Dave','Betsy'), ('Jacob','Betsy'), ('Risha','David')
, ('Jospeh','David'), ('Dandy','Phillip'), ('Andrew','Phillip')
, ('Henry','Harvey'), ('Nelson','Harvey'), ('Simon','Paul')
, ('Casper','Paul'), ('Betsy','Joe'), ('David','Joe')
, ('Phillip','Joe'), ('Harvey','Joe'), ('Paul','Joe')
它的輸出是：
Employee_name Supervisor_name
Frank Andrew
Howard Andrew
Ralph Andrew
Dave Betsy
Jacob Betsy
Hazel Casper
Irene Casper
Martha Casper
Fred Dandy
Joshua Dandy
Silas Dandy
Alice Dave
Barton Dave
Olive Dave
Jospeh David
Risha David
Henry Harvey
Nelson Harvey
Alex Henry
Dennis Henry
Floyd Henry
Almira Jacob
Charles Jacob
Davis Jacob
Betsy Joe
David Joe
....
結果應該在從低到高的層次結構中，例如：
Employee_Name Supervisor_Name Higher_Supervisor Next_higher_Supervisor
Frank Andrew Phillip Joe
Howard Andrew Phillip Joe
Ralph Andrew Phillip Joe
Dave Betsy Joe no_supervisor
Jacob Betsy Joe no_supervisor
Hazel Casper Paul Joe
Irene Casper Paul Joe
Martha Casper Paul Joe
For Eg: Frank's supervisor is Andrew, Andrew's supervisor is Phillip, Phillip's supervisor is Joe
For Eg: Dave's supervisor is Betsy, Betsy's supervisor is Joe, and Joe doesn't have any supervisor so no_supervisor should be displayed.
For Eg: Hazel's supervisor is Casper, Casper's supervisor's is Paul, and Paul's Supervisor is Joe should be displayed in the order format
uj5u.com熱心網友回復：
對于這組特定的資料，它可以LEFT JOIN通過表格本身來獲得預期的結果
SELECT a.Employee_Name, a.Supervisor_Name, b.Supervisor_Name, c.Supervisor_Name
FROM DATABASE_TABLE a
LEFT JOIN DATABASE_TABLE b ON a.Supervisor_Name = b.Employee_Name
LEFT JOIN DATABASE_TABLE c ON b.Supervisor_Name = c.Employee_Name
如果層次深度未知，即列數未知，則更復雜。仍然可以通過使用遞回 CTE 來查找深度并生成動態 SQL。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/361090.html