-- 员工表

create table employee (

id bigint primary key auto_increment,

student_no varchar(10) unique,

name varchar(10),

gender char(1) ,

age tinyint unsigned check (age between 0 and 255),

identity_id varchar(18) unique ,

entrydate date

)

desc employee;

-- 先添加逻辑删除字段

ALTER TABLE employee ADD COLUMN is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除标志：0-未删除，1-已删除';

-- 先添加创建时间字段

ALTER TABLE employee ADD COLUMN create_time TINYINT DEFAULT 0 COMMENT '创建时间';

-- 先添加更新时间字段

ALTER TABLE employee ADD COLUMN update_time TINYINT DEFAULT 0 COMMENT '更新时间';

-- 修改创建时间字段类型为 DATE

ALTER TABLE employee ADD COLUMN create_time DATE DEFAULT NULL COMMENT '创建时间';

-- 修改更新时间字段类型为 DATE

ALTER TABLE employee ADD COLUMN update_time DATE DEFAULT NULL COMMENT '更新时间';

MODIFY COLUMN create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

MODIFY COLUMN update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

begin transaction;

INSERT INTO employee (student_no, name, gender, age, identity_id, entrydate) VALUES

('YT001', '张无忌', '男', 22, '110101199802150012', '2020-03-15'),

('YT002', '赵敏', '女', 20, '110101200002280023', '2020-03-20'),

('YT003', '周芷若', '女', 21, '110101199902140034', '2020-03-18'),

('YT004', '小昭', '女', 18, '110101200202150045', '2020-04-01'),

('YT005', '殷离', '女', 19, '110101200102160056', '2020-03-25'),

('YT006', '杨逍', '男', 35, '110101198702150067', '2020-02-10'),

('YT007', '范遥', '男', 34, '110101198802160078', '2020-02-12'),

('YT008', '谢逊', '男', 45, '110101197702150089', '2020-01-20'),

('YT009', '殷天正', '男', 50, '110101197202150090', '2020-01-15'),

('YT010', '韦一笑', '男', 40, '110101198202150101', '2020-02-05'),

('YT011', '宋青书', '男', 23, '110101199702150112', '2020-03-22'),

('YT012', '灭绝师太', '女', 48, '110101197402150123', '2020-01-25');

commit transaction;

INSERT INTO employee (student_no, name, gender, age, identity_id, entrydate) VALUES

('YT013', '张三丰', '男', 110, '110101191402150134', '2020-01-05'),

('YT014', '殷素素', '女', 35, '110101198702150145', '2020-02-08'),

('YT015', '张翠山', '男', 38, '110101198402150156', '2020-02-08'),

('YT016', '成昆', '男', 55, '110101196702150167', '2020-01-18'),

('YT017', '空见神僧', '男', 60, '110101196202150178', '2020-01-10'),

('YT018', '黛绮丝', '女', 42, '110101198002150189', '2020-02-15'),

('YT019', '何太冲', '男', 48, '110101197402150190', '2020-01-28'),

('YT020', '班淑娴', '女', 46, '110101197602150201', '2020-01-28'),

('YT021', '常遇春', '男', 28, '110101199402150212', '2020-03-08'),

('YT022', '徐达', '男', 30, '110101199202150223', '2020-03-05'),

('YT023', '朱元璋', '男', 32, '110101199002150234', '2020-03-02'),

('YT024', '杨不悔', '女', 17, '110101200502150245', '2020-04-05'),

('YT025', '殷梨亭', '男', 40, '110101198202150256', '2020-02-18'),

('YT026', '俞莲舟', '男', 45, '110101197702150267', '2020-02-12'),

('YT027', '宋远桥', '男', 48, '110101197402150278', '2020-02-10'),

('YT028', '张松溪', '男', 42, '110101198002150289', '2020-02-15'),

('YT029', '莫声谷', '男', 35, '110101198702150290', '2020-02-20'),

('YT030', '丁敏君', '女', 25, '110101199702150301', '2020-03-28'),

('YT031', '纪晓芙', '女', 30, '110101199202150312', '2020-03-12'),

('YT032', '胡青牛', '男', 52, '110101197002150323', '2020-01-30'),

('YT033', '王难姑', '女', 50, '110101197202150334', '2020-01-30'),

('YT034', '说不得', '男', 38, '110101198402150345', '2020-02-22'),

('YT035', '冷谦', '男', 44, '110101197802150356', '2020-02-14'),

('YT036', '周颠', '男', 42, '110101198002150367', '2020-02-16'),

('YT037', '彭莹玉', '男', 46, '110101197602150378', '2020-02-08'),

('YT038', '铁冠道人', '男', 48, '110101197402150389', '2020-02-06'),

('YT039', '阳顶天', '男', 58, '110101196402150390', '2020-01-08'),

('YT040', '韩千叶', '男', 36, '110101198602150401', '2020-02-25');

INSERT INTO employee (student_no, name, gender, age, identity_id, entrydate) VALUES

('YT041', '阿大', '男', 48, '110101197402150412', '2020-01-30'),

('YT042', '阿二', '男', 46, '110101197602150423', '2020-01-30'),

('YT043', '阿三', '男', 44, '110101197802150434', '2020-01-30'),

('YT044', '方东白', '男', 48, '110101197402150445', '2020-01-30'),

('YT045', '鹤笔翁', '男', 55, '110101196702150456', '2020-01-22'),

('YT046', '鹿杖客', '男', 56, '110101196602150467', '2020-01-22'),

('YT047', '史火龙', '男', 42, '110101198002150478', '2020-02-18'),

('YT048', '传功长老', '男', 50, '110101197202150489', '2020-02-14'),

('YT049', '执法长老', '男', 48, '110101197402150490', '2020-02-14'),

('YT050', '掌棒龙头', '男', 45, '110101197702150501', '2020-02-16'),

('YT051', '掌钵龙头', '男', 46, '110101197602150512', '2020-02-16'),

('YT052', '金花婆婆', '女', 42, '110101198002150523', '2020-02-20'),

('YT053', '殷野王', '男', 40, '110101198202150534', '2020-02-22'),

('YT054', '李天垣', '男', 52, '110101197002150545', '2020-02-10'),

('YT055', '静玄师太', '女', 35, '110101198702150556', '2020-03-05'),

('YT056', '静虚师太', '女', 33, '110101198902150567', '2020-03-08'),

('YT057', '贝锦仪', '女', 26, '110101199602150578', '2020-03-25'),

('YT058', '苏梦清', '女', 24, '110101199802150589', '2020-03-28');

SELECT gender,count(gender) FROM employee group by gender;

CREATE TABLE employer (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

company_no VARCHAR(20) UNIQUE NOT NULL COMMENT '企业编号',

company_name VARCHAR(100) NOT NULL COMMENT '企业名称',

industry VARCHAR(50) COMMENT '所属行业',

company_size VARCHAR(20) COMMENT '企业规模',

address VARCHAR(200) COMMENT '企业地址',

contact_person VARCHAR(20) COMMENT '联系人',

contact_phone VARCHAR(20) COMMENT '联系电话',

email VARCHAR(50) COMMENT '邮箱',

establish_date DATE COMMENT '成立日期',

registered_capital DECIMAL(15,2) COMMENT '注册资本（万元）',

business_license VARCHAR(30) UNIQUE COMMENT '营业执照号',

status TINYINT DEFAULT 1 COMMENT '状态：1-正常，2-暂停，3-注销',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除：0-未删除，1-已删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

);

INSERT INTO employer (company_no, company_name, industry, company_size, address, contact_person, contact_phone, email, establish_date, registered_capital, business_license) VALUES

('COMP001', '明教集团', '武术培训', '大型', '光明顶总部', '杨逍', '13800138001', 'yangxiao@mingjiao.com', '1350-05-20', 5000.00, '913100001357001001'),

('COMP002', '武当山武术学院', '教育行业', '中型', '湖北省武当山', '宋远桥', '13800138002', 'songyuanqiao@wudang.com', '1270-08-15', 3000.00, '913100001357001002'),

('COMP003', '峨眉派文化传播', '文化传媒', '中型', '四川省峨眉山', '灭绝师太', '13800138003', 'mjst@emei.com', '1280-03-10', 2000.00, '913100001357001003'),

('COMP004', '丐帮人力资源', '人力资源', '大型', '全国各地分部', '史火龙', '13800138004', 'shihuolong@gaibang.com', '1250-11-25', 1000.00, '913100001357001004'),

('COMP005', '天鹰教安保', '安保服务', '中型', '浙江省天鹰山', '殷天正', '13800138005', 'yintianzheng@tianying.com', '1320-07-08', 1500.00, '913100001357001005'),

('COMP006', '西域金刚门建设', '建筑工程', '小型', '西域地区', '阿二', '13800138006', 'aer@jingangmen.com', '1340-02-14', 800.00, '913100001357001006'),

('COMP007', '汝阳王府投资', '投资金融', '大型', '大都王府', '赵敏', '13800138007', 'zhaomin@ruyang.com', '1335-09-30', 10000.00, '913100001357001007');

-- 添加employer_id字段

ALTER TABLE employee ADD COLUMN employer_id BIGINT COMMENT '雇主ID，关联employer表';

-- 添加外键约束（可选）

ALTER TABLE employee ADD CONSTRAINT fk_employee_employer

FOREIGN KEY (employer_id) REFERENCES employer(id);

-- 明教集团员工

UPDATE employee SET employer_id = 1 WHERE name IN ('张无忌', '杨逍', '范遥', '谢逊', '殷天正', '韦一笑', '小昭', '殷离', '黛绮丝', '说不得', '冷谦', '周颠', '彭莹玉', '铁冠道人', '阳顶天');

-- 武当山武术学院员工

UPDATE employee SET employer_id = 2 WHERE name IN ('张三丰', '宋远桥', '俞莲舟', '张松溪', '殷梨亭', '莫声谷', '宋青书');

-- 峨眉派文化传播员工

UPDATE employee SET employer_id = 3 WHERE name IN ('周芷若', '灭绝师太', '丁敏君', '纪晓芙', '静玄师太', '静虚师太', '贝锦仪', '苏梦清');

-- 丐帮人力资源员工

UPDATE employee SET employer_id = 4 WHERE name IN ('史火龙', '传功长老', '执法长老', '掌棒龙头', '掌钵龙头');

-- 天鹰教安保员工

UPDATE employee SET employer_id = 5 WHERE name IN ('殷素素', '张翠山', '殷野王', '李天垣');

-- 西域金刚门建设员工

UPDATE employee SET employer_id = 6 WHERE name IN ('阿大', '阿二', '阿三', '成昆', '方东白');

-- 汝阳王府投资员工

UPDATE employee SET employer_id = 7 WHERE name IN ('赵敏', '鹤笔翁', '鹿杖客', '常遇春', '徐达', '朱元璋');

-- 神医毒仙等自由职业者暂时不分配雇主

UPDATE employee SET employer_id = NULL WHERE name IN ('胡青牛', '王难姑', '金花婆婆');

-- 查询每个企业的员工列表

SELECT

e.company_name AS '企业名称',

emp.name AS '员工姓名',

emp.gender AS '性别',

emp.age AS '年龄',

emp.student_no AS '工号'

FROM employer e

LEFT JOIN employee emp ON e.id = emp.employer_id

WHERE e.is_deleted = 0 AND emp.is_deleted = 0

ORDER BY e.company_name, emp.name;

-- 统计每个企业的员工数量

SELECT

e.company_name AS '企业名称',

COUNT(emp.id) AS '员工数量',

e.company_size AS '企业规模'

FROM employer e

LEFT JOIN employee emp ON e.id = emp.employer_id AND emp.is_deleted = 0

WHERE e.is_deleted = 0

GROUP BY e.id, e.company_name, e.company_size

ORDER BY COUNT(emp.id) DESC;

-- 查询比灭绝徒弟更多的掌门人

-- 先查询灭绝的徒弟数量

SELECT count(*) as '灭绝师太徒弟数量' from employee e WHERE employer_id = 3 and name != '灭绝师太' and is_deleted = 0;

-- 第二步：查询比灭绝徒弟更多的掌门人

WITH employer_employee_count AS (

SELECT

e.id AS employer_id,

e.company_name AS 门派名称,

e.contact_person AS 掌门人,

COUNT(emp.id) AS 弟子数量

FROM employer e

LEFT JOIN employee emp ON e.id = emp.employer_id AND emp.is_deleted = 0 AND emp.name != e.contact_person

WHERE e.is_deleted = 0

GROUP BY e.id, e.company_name, e.contact_person

),

灭绝弟子数 AS (

SELECT 弟子数量 AS 灭绝弟子数

FROM employer_employee_count

WHERE 掌门人 = '灭绝师太'

)

SELECT

eec.门派名称,

eec.掌门人,

eec.弟子数量,

e.company_size AS 门派规模,

e.industry AS 行业

FROM employer_employee_count eec

JOIN employer e ON eec.employer_id = e.id

CROSS JOIN 灭绝弟子数

WHERE eec.弟子数量 > 灭绝弟子数.灭绝弟子数

ORDER BY eec.弟子数量 DESC;

-- 第三步：详细的各门派弟子统计

SELECT

e.company_name AS 门派,

e.contact_person AS 掌门人,

COUNT(emp.id) AS 弟子数量,

e.company_size AS 门派规模,

GROUP_CONCAT(emp.name ORDER BY emp.entrydate SEPARATOR ', ') AS 弟子名单

FROM employer e

LEFT JOIN employee emp ON e.id = emp.employer_id

AND emp.is_deleted = 0

AND emp.name != e.contact_person -- 排除掌门人自己

WHERE e.is_deleted = 0

AND e.contact_person IS NOT NULL -- 确保有掌门人

GROUP BY e.id, e.company_name, e.contact_person, e.company_size

HAVING COUNT(emp.id) > (

SELECT COUNT(*)

FROM employee

WHERE employer_id = 3

AND name != '灭绝师太'

AND is_deleted = 0

)

ORDER BY 弟子数量 DESC;

-- 第四步：完整的门派弟子排名

SELECT

e.company_name AS 门派,

e.contact_person AS 掌门人,

COUNT(emp.id) AS 弟子数量,

e.company_size AS 门派规模,

RANK() OVER (ORDER BY COUNT(emp.id) DESC) AS 弟子数量排名

FROM employer e

LEFT JOIN employee emp ON e.id = emp.employer_id

AND emp.is_deleted = 0

AND emp.name != e.contact_person

WHERE e.is_deleted = 0

AND e.contact_person IS NOT NULL

GROUP BY e.id, e.company_name, e.contact_person, e.company_size

ORDER BY 弟子数量 DESC;

-- 部门表

CREATE TABLE department (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

department_no VARCHAR(20) UNIQUE NOT NULL COMMENT '部门编号',

department_name VARCHAR(50) NOT NULL COMMENT '部门名称',

employer_id BIGINT NOT NULL COMMENT '所属企业ID',

manager_id BIGINT COMMENT '部门经理ID',

parent_id BIGINT COMMENT '上级部门ID',

description TEXT COMMENT '部门描述',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (employer_id) REFERENCES employer(id),

FOREIGN KEY (manager_id) REFERENCES employee(id),

FOREIGN KEY (parent_id) REFERENCES department(id)

);

-- 职位表

-- 使用job_position作为表名

CREATE TABLE job_position (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

position_no VARCHAR(20) UNIQUE NOT NULL COMMENT '职位编号',

position_name VARCHAR(50) NOT NULL COMMENT '职位名称',

department_id BIGINT NOT NULL COMMENT '所属部门ID',

position_level VARCHAR(20) COMMENT '职位等级',

min_salary DECIMAL(10,2) COMMENT '最低薪资',

max_salary DECIMAL(10,2) COMMENT '最高薪资',

job_description TEXT COMMENT '职位描述',

requirements TEXT COMMENT '任职要求',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (department_id) REFERENCES department(id)

);

-- 重新创建employee_position表

-- 创建employee_position表

CREATE TABLE employee_position (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

employee_id BIGINT NOT NULL COMMENT '员工ID',

position_id BIGINT NOT NULL COMMENT '职位ID',

start_date DATE NOT NULL COMMENT '任职开始日期',

end_date DATE COMMENT '任职结束日期',

salary DECIMAL(10,2) COMMENT '薪资',

status VARCHAR(20) DEFAULT '在职' COMMENT '状态：在职、离职、调动中',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (employee_id) REFERENCES employee(id),

FOREIGN KEY (position_id) REFERENCES job_position(id)

);

DROP TABLE IF EXISTS employee_position;

DROP TABLE IF EXISTS position;

-- 考勤表 (attendance)

CREATE TABLE attendance (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

employee_id BIGINT NOT NULL COMMENT '员工ID',

work_date DATE NOT NULL COMMENT '工作日期',

check_in_time DATETIME COMMENT '上班打卡时间',

check_out_time DATETIME COMMENT '下班打卡时间',

work_hours DECIMAL(4,2) COMMENT '工作时长',

status VARCHAR(20) DEFAULT '正常' COMMENT '考勤状态：正常、迟到、早退、缺勤等',

remark VARCHAR(200) COMMENT '备注',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (employee_id) REFERENCES employee(id),

UNIQUE KEY uk_employee_date (employee_id, work_date)

);

-- 薪资表 (salary)

CREATE TABLE salary (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

employee_id BIGINT NOT NULL COMMENT '员工ID',

salary_month VARCHAR(7) NOT NULL COMMENT '薪资月份 yyyy-MM',

base_salary DECIMAL(10,2) COMMENT '基本工资',

bonus DECIMAL(10,2) DEFAULT 0 COMMENT '奖金',

allowance DECIMAL(10,2) DEFAULT 0 COMMENT '津贴',

overtime_pay DECIMAL(10,2) DEFAULT 0 COMMENT '加班费',

deduction DECIMAL(10,2) DEFAULT 0 COMMENT '扣款',

net_salary DECIMAL(10,2) COMMENT '实发工资',

status VARCHAR(20) DEFAULT '未发放' COMMENT '状态：未发放、已发放',

pay_date DATE COMMENT '发放日期',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (employee_id) REFERENCES employee(id),

UNIQUE KEY uk_employee_month (employee_id, salary_month)

);

-- 请假表 (leave_request)

CREATE TABLE leave_request (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

employee_id BIGINT NOT NULL COMMENT '员工ID',

leave_type VARCHAR(20) NOT NULL COMMENT '请假类型：年假、病假、事假等',

start_date DATE NOT NULL COMMENT '开始日期',

end_date DATE NOT NULL COMMENT '结束日期',

leave_days DECIMAL(3,1) NOT NULL COMMENT '请假天数',

reason TEXT COMMENT '请假原因',

status VARCHAR(20) DEFAULT '待审批' COMMENT '状态：待审批、已批准、已拒绝',

approver_id BIGINT COMMENT '审批人ID',

approve_time DATETIME COMMENT '审批时间',

approve_remark VARCHAR(200) COMMENT '审批意见',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (employee_id) REFERENCES employee(id),

FOREIGN KEY (approver_id) REFERENCES employee(id)

);

-- 项目表

CREATE TABLE project (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

project_no VARCHAR(20) UNIQUE NOT NULL COMMENT '项目编号',

project_name VARCHAR(100) NOT NULL COMMENT '项目名称',

employer_id BIGINT NOT NULL COMMENT '所属企业ID',

manager_id BIGINT COMMENT '项目经理ID',

start_date DATE COMMENT '开始日期',

end_date DATE COMMENT '结束日期',

budget DECIMAL(12,2) COMMENT '项目预算',

status VARCHAR(20) DEFAULT '进行中' COMMENT '项目状态',

description TEXT COMMENT '项目描述',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (employer_id) REFERENCES employer(id),

FOREIGN KEY (manager_id) REFERENCES employee(id)

);

-- 项目成员表 (project_member)

CREATE TABLE project_member (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

project_id BIGINT NOT NULL COMMENT '项目ID',

employee_id BIGINT NOT NULL COMMENT '成员ID',

role VARCHAR(50) COMMENT '项目角色',

join_date DATE COMMENT '加入日期',

leave_date DATE COMMENT '离开日期',

workload_percent TINYINT COMMENT '工作量百分比',

is_deleted TINYINT DEFAULT 0 COMMENT '逻辑删除',

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (project_id) REFERENCES project(id),

FOREIGN KEY (employee_id) REFERENCES employee(id),

UNIQUE KEY uk_project_employee (project_id, employee_id)

);

-- 插入部门数据

INSERT INTO department (department_no, department_name, employer_id, manager_id, description) VALUES

('DEPT001', '教主办公室', 1, 6, '明教最高领导机构'),

('DEPT002', '光明左右使', 1, 6, '明教核心管理层'),

('DEPT003', '四大法王', 1, 8, '明教四大护教法王'),

('DEPT004', '五散人', 1, 34, '明教五散人'),

('DEPT005', '武当长老院', 2, 13, '武当派长老机构'),

('DEPT006', '武当七侠', 2, 27, '武当七侠团队'),

('DEPT007', '峨眉剑堂', 3, 12, '峨眉派剑法传承'),

('DEPT008', '峨眉弟子院', 3, 55, '峨眉弟子管理机构'),

('DEPT009', '丐帮长老会', 4, 47, '丐帮长老管理机构'),

('DEPT010', '天鹰教核心', 5, 9, '天鹰教核心团队'),

('DEPT011', '金刚门武堂', 6, 42, '西域金刚门武术堂'),

('DEPT012', '王府护卫队', 7, 45, '汝阳王府护卫队伍');

-- 插入职位数据

INSERT INTO job_position (position_no, position_name, department_id, position_level, min_salary, max_salary) VALUES

('POS001', '教主', 1, 'P1', 10000.00, 50000.00),

('POS002', '光明左使', 2, 'P2', 8000.00, 30000.00),

('POS003', '光明右使', 2, 'P2', 8000.00, 30000.00),

('POS004', '金毛狮王', 3, 'P3', 6000.00, 20000.00),

('POS005', '白眉鹰王', 3, 'P3', 6000.00, 20000.00),

('POS006', '青翼蝠王', 3, 'P3', 6000.00, 20000.00),

('POS007', '紫衫龙王', 3, 'P3', 6000.00, 20000.00),

('POS008', '五散人首领', 4, 'P4', 5000.00, 15000.00),

('POS009', '五散人成员', 4, 'P4', 4000.00, 12000.00),

('POS010', '武当掌门', 5, 'P1', 9000.00, 40000.00),

('POS011', '武当长老', 5, 'P2', 7000.00, 25000.00),

('POS012', '武当七侠', 6, 'P3', 6000.00, 20000.00),

('POS013', '峨眉掌门', 7, 'P1', 8000.00, 35000.00),

('POS014', '峨眉大师姐', 8, 'P2', 5000.00, 18000.00),

('POS015', '峨眉弟子', 8, 'P3', 3000.00, 10000.00),

('POS016', '丐帮帮主', 9, 'P1', 7000.00, 30000.00),

('POS017', '丐帮长老', 9, 'P2', 5000.00, 15000.00),

('POS018', '天鹰教主', 10, 'P1', 8000.00, 35000.00),

('POS019', '天鹰护法', 10, 'P2', 5000.00, 18000.00),

('POS020', '金刚门主', 11, 'P1', 6000.00, 25000.00),

('POS021', '金刚门徒', 11, 'P2', 4000.00, 12000.00),

('POS022', '王府郡主', 12, 'P1', 9000.00, 40000.00),

('POS023', '王府护卫', 12, 'P2', 5000.00, 15000.00);

-- 插入员工职位关联数据

INSERT INTO employee_position (employee_id, position_id, start_date, salary, status) VALUES

(1, 1, '2020-03-15', 30000.00, '在职'), -- 张无忌 - 教主

(6, 2, '2020-02-10', 20000.00, '在职'), -- 杨逍 - 光明左使

(7, 3, '2020-02-12', 20000.00, '在职'), -- 范遥 - 光明右使

(8, 4, '2020-01-20', 15000.00, '在职'), -- 谢逊 - 金毛狮王

(9, 5, '2020-01-15', 15000.00, '在职'), -- 殷天正 - 白眉鹰王

(10, 6, '2020-02-05', 15000.00, '在职'), -- 韦一笑 - 青翼蝠王

(18, 7, '2020-02-15', 15000.00, '在职'), -- 黛绮丝 - 紫衫龙王

(34, 8, '2020-02-22', 12000.00, '在职'), -- 说不得 - 五散人首领

(35, 9, '2020-02-14', 10000.00, '在职'), -- 冷谦 - 五散人成员

(36, 9, '2020-02-16', 10000.00, '在职'), -- 周颠 - 五散人成员

(37, 9, '2020-02-08', 10000.00, '在职'), -- 彭莹玉 - 五散人成员

(38, 9, '2020-02-06', 10000.00, '在职'), -- 铁冠道人 - 五散人成员

(13, 10, '2020-01-05', 25000.00, '在职'), -- 张三丰 - 武当掌门

(27, 11, '2020-02-10', 12000.00, '在职'), -- 宋远桥 - 武当长老

(25, 12, '2020-02-18', 10000.00, '在职'), -- 殷梨亭 - 武当七侠

(26, 12, '2020-02-12', 10000.00, '在职'), -- 俞莲舟 - 武当七侠

(28, 12, '2020-02-15', 10000.00, '在职'), -- 张松溪 - 武当七侠

(29, 12, '2020-02-20', 10000.00, '在职'), -- 莫声谷 - 武当七侠

(12, 13, '2020-01-25', 20000.00, '在职'), -- 灭绝师太 - 峨眉掌门

(3, 14, '2020-03-18', 8000.00, '在职'), -- 周芷若 - 峨眉大师姐

(30, 15, '2020-03-28', 5000.00, '在职'), -- 丁敏君 - 峨眉弟子

(55, 15, '2020-03-05', 5000.00, '在职'), -- 静玄师太 - 峨眉弟子

(47, 16, '2020-02-18', 15000.00, '在职'), -- 史火龙 - 丐帮帮主

(48, 17, '2020-02-14', 8000.00, '在职'), -- 传功长老 - 丐帮长老

(9, 18, '2020-01-15', 18000.00, '在职'), -- 殷天正 - 天鹰教主

(53, 19, '2020-02-22', 8000.00, '在职'), -- 殷野王 - 天鹰护法

(42, 20, '2020-01-30', 12000.00, '在职'), -- 阿二 - 金刚门主

(41, 21, '2020-01-30', 6000.00, '在职'), -- 阿大 - 金刚门徒

(2, 22, '2020-03-20', 25000.00, '在职'), -- 赵敏 - 王府郡主

(45, 23, '2020-01-22', 8000.00, '在职'); -- 鹤笔翁 - 王府护卫

-- 查询员工完整信息

SELECT

e.name AS '员工姓名',

e.gender AS '性别',

e.age AS '年龄',

emp.company_name AS '企业名称',

d.department_name AS '部门',

jp.position_name AS '职位',

ep.salary AS '薪资',

ep.start_date AS '任职时间'

FROM employee e

LEFT JOIN employer emp ON e.employer_id = emp.id

LEFT JOIN employee_position ep ON e.id = ep.employee_id AND ep.status = '在职'

LEFT JOIN job_position jp ON ep.position_id = jp.id

LEFT JOIN department d ON jp.department_id = d.id

WHERE e.is_deleted = 0

ORDER BY emp.company_name, d.department_name, jp.position_level;

-- 查询各部门薪资统计

SELECT

emp.company_name AS '企业',

d.department_name AS '部门',

COUNT(DISTINCT e.id) AS '员工数量',

AVG(ep.salary) AS '平均薪资',

SUM(ep.salary) AS '总薪资支出'

FROM department d

JOIN employer emp ON d.employer_id = emp.id

LEFT JOIN job_position jp ON jp.department_id = d.id

LEFT JOIN employee_position ep ON jp.id = ep.position_id AND ep.status = '在职'

LEFT JOIN employee e ON ep.employee_id = e.id

WHERE d.is_deleted = 0 AND emp.is_deleted = 0

GROUP BY emp.company_name, d.department_name

ORDER BY emp.company_name, SUM(ep.salary) DESC;

-- 3. 企业组织架构查询

SELECT

emp.company_name AS '企业',

d.department_name AS '部门',

jp.position_name AS '职位',

e.name AS '任职人员',

m.name AS '部门经理'

FROM department d

JOIN employer emp ON d.employer_id = emp.id

LEFT JOIN job_position jp ON jp.department_id = d.id

LEFT JOIN employee_position ep ON jp.id = ep.position_id AND ep.status = '在职'

LEFT JOIN employee e ON ep.employee_id = e.id

LEFT JOIN employee m ON d.manager_id = m.id

WHERE d.is_deleted = 0 AND emp.is_deleted = 0

ORDER BY emp.company_name, d.department_name, jp.position_level;

--

-- 1. FROM

-- 2. ON

-- 3. JOIN

-- 4. WHERE

-- 5. GROUP BY

-- 6. WITH CUBE/ROLLUP

-- 7. HAVING

-- 8. SELECT

-- 9. DISTINCT

-- 10. ORDER BY

-- 11. LIMIT/OFFSET

select invitee_email from identity_invitee_record iir where invite_code = 'D0001' and invitee_email = '78895554@gmail.com';