描述
【背景】：物流公司想要分析快递小哥的薪资构成和绩效情况，以便制定更合理的薪酬政策。
【原始表】：
couriers_info （快递员）表:
- courier_id (快递员 ID): 快递员的唯一标识符，INT
- courier_name (快递员姓名): 快递员的姓名，VARCHAR(50)
- base_salary (基本工资): 快递员的基本工资，DECIMAL(10, 2)
deliveries_info （派送）表:
- delivery_id (派送 ID): 派送任务的唯一标识符，INT
- courier_id (快递员 ID): 关联快递员表的快递员 ID，INT
- delivery_date (派送日期): 派送的日期，DATE
- delivery_fee (派送费用): 每次派送的费用，DECIMAL(10, 2)
expenses_info （支出）表：
- expense_id：支出条目的唯一标识符，INT
- courier_id：与 couriers_info 表中的 courier_id 相关联，快递员的唯一标识符，INT
- expense_date：支出发生的日期，DATE
- expense_amount：支出的金额，DECIMAL(10, 2)
- expense_reason：支出的原因或用途，VARCHAR(100)
【要求】：根据上述表格，查询出每个快递员在 2024 年 7 月的总收入（基本工资 + 派送费用总和 - 支出 ）。查询结果按照快递员 ID 升序排列。要求查询出来的表格的字段如下：
- courier_id: 快递员的唯一标识符。
- courier_name: 快递员的姓名。
- total_income: 快递员2024 年 7 月的总收入。
【示例】
couriers_info （快递员）表:
deliveries_info （派送）表:
expenses_info （支出）表：
【按要求查出来的表】
【解释】
上述示例中，courier_id是1的员工是Alice，他在2024年7月份的总收入 = 2000 + 50+50 = 2100，支出是30+20 = 50，所以他在2024年7月份的总收入是2100 - 50 = 2050
示例1
输入：
DROP TABLE IF EXISTS couriers_info; DROP TABLE IF EXISTS deliveries_info; DROP TABLE IF EXISTS expenses_info; CREATE TABLE couriers_info ( courier_id INT PRIMARY KEY, courier_name VARCHAR(50), base_salary DECIMAL(10, 2) ); CREATE TABLE deliveries_info ( delivery_id INT PRIMARY KEY, courier_id INT, delivery_date DATE, delivery_fee DECIMAL(10, 2) ); CREATE TABLE expenses_info ( expense_id INT PRIMARY KEY, courier_id INT, expense_date DATE, expense_amount DECIMAL(10, 2), expense_reason VARCHAR(100) ); INSERT INTO couriers_info (courier_id, courier_name, base_salary) VALUES (1, 'Alice', 2000.00), (2, 'Bob', 1800.00); INSERT INTO deliveries_info (delivery_id, courier_id, delivery_date, delivery_fee) VALUES (1, 1, '2024-07-01', 50.00), (2, 1, '2024-07-05', 50.00), (3, 2, '2024-06-03', 40.00), (4, 2, '2024-07-10', 60.00), (5, 2, '2024-07-10', 60.00); INSERT INTO expenses_info (expense_id, courier_id, expense_date, expense_amount, expense_reason) VALUES (1, 1, '2024-07-02', 30.00, 'Uniform purchase'), (2, 1, '2024-07-08', 20.00, 'Fuel expenses'), (3, 2, '2024-07-05', 25.00, 'Vehicle maintenance'), (4, 2, '2024-07-12', 15.00, 'Miscellaneous expenses'); select * from couriers_info; select * from deliveries_info; select * from expenses_info;
复制输出：
courier_id|courier_name|total_income 1|Alice|2050.00 2|Bob|1880.00
with t1 as ( select distinct courier_id ,sum(delivery_fee) over (partition by courier_id) as t1 from deliveries_info where delivery_date between '2024-07-01' and '2024-07-31' ), t2 as ( select distinct courier_id,sum(expense_amount) over(partition by courier_id) as t2 from expenses_info where expense_date between '2024-07-01' and '2024-07-31' ) select couriers_info.courier_id,courier_name,round(t1.t1+couriers_info.base_salary-t2.t2,2) as total_income from t2 join t1 on t1.courier_id=t2.courier_id join couriers_info on t1.courier_id =couriers_info.courier_id order by courier_id
