SQL高级难题-复杂的员工项目任务分配查询挑战(无答案)

给定如下的数据库表 Employee 和 Project，以及它们之间的关联表 Assignment：

Employee 表

字段	类型	描述
employee_id	INT	员工的唯一 ID
name	VARCHAR	员工姓名
department	VARCHAR	所属部门

Project 表

字段	类型	描述
project_id	INT	项目的唯一 ID
project_name	VARCHAR	项目名称
start_date	DATE	项目开始日期
end_date	DATE	项目结束日期

Assignment 表

字段	类型	描述
assignment_id	INT	任务的唯一 ID
employee_id	INT	员工 ID，关联到 Employee
project_id	INT	项目 ID，关联到 Project
hours	INT	员工在该项目上投入的时间（小时）

要求：

1. 找出每位员工在每个季度的项目投入时间总和。如果员工某个季度没有投入时间，则显示为 0。季度可以定义为：

   • Q1: 1月到3月
   • Q2: 4月到6月
   • Q3: 7月到9月
   • Q4: 10月到12月

2. 找出在每个季度中每个部门的员工总投入时间，按季度和部门输出，并根据部门的总投入时间进行排序。

3. 输出每个项目的进展情况：计算项目的预定进度（根据当前日期与项目开始、结束日期），并找出每个项目的实际完成比例（员工投入时间除以项目计划时长）。输出项目名称、预定进度、实际进展比例，并标明项目进展状态（按以下规则）：

   • "落后"：如果实际进展 < 预定进度的80%
   • "正常"：如果实际进展在预定进度的80%-120%之间
   • "超前"：如果实际进展 > 预定进度的120%

输出要求：

1. 第一部分的结果：输出员工 ID、员工姓名、季度（Q1/Q2/Q3/Q4）、投入时间。
2. 第二部分的结果：输出季度、部门、部门总投入时间（按降序排序）。
3. 第三部分的结果：输出项目名称、预定进展比例、实际进展比例、项目状态。

你可以通过编写一段 MySQL 查询语句，完成所有这三个输出要求。注意使用窗口函数、子查询、CTE 或者其他适合的 SQL 技术，尽量避免使用硬编码来解决这个问题。

这个题目综合考察了对多表联结、窗口函数、时间处理、复杂聚合、以及逻辑判断的运用，是一个比较有挑战性的综合 SQL 题目，适合深入学习 MySQL 的高级特性。

建表语句:

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL
);
CREATE TABLE Project (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL
);
CREATE TABLE Assignment (
    assignment_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    hours INT NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES Employee(employee_id),
    FOREIGN KEY (project_id) REFERENCES Project(project_id)
);