编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE department(
    id bigint primary key,
    name varchar(100) not null
);

INSERT into department(id, name) values(110101, 'Tech Department');
INSERT into department(id, name) values(110102, 'HR Department');
INSERT into department(id, name) values(110103, 'Marketing Department');
INSERT into department(id, name) values(110104, 'Finance Department');
INSERT into department(id, name) values(110105, 'Sales Department');

CREATE TABLE employee (
    id bigint primary key,
    name varchar(100) not null,
    work_place varchar(100) not null,
    gender enum('male', 'female', 'other') not null,
    phone varchar(20),
    department_id bigint,
    leader_id bigint
);

INSERT INTO employee(id, name, work_place, gender, phone, department_id, leader_id)
values(20200101, 'Jack', '123 Main St.', 'Male', '13945675710', 110101, null);
INSERT INTO employee(id, name, work_place, gender, phone, department_id, leader_id)
values(20220112, 'Jone', '123 Main St.', 'Male', '12345678910', 110101, 20200101);
INSERT INTO employee(id, name, work_place, gender, phone, department_id, leader_id)
values(20150101, 'Alice', '303 Pine St.', 'Female', '13945675710', 110102, null);
INSERT INTO employee(id, name, work_place, gender, phone, department_id, leader_id)
values(20220205, 'Kimi', '303 Pine St.', 'Male', '15145671101', 110102, 20150101);

SELECT 
    e.name AS employee_name,
    e.work_place,
    d.name AS department_name,
    l.name AS leader_name
FROM 
    employee e
LEFT JOIN 
    department d ON e.department_id = d.id
LEFT JOIN 
    employee l ON e.leader_id = l.id
ORDER BY 
    d.name DESC;