物流公司想要分析快递小哥的薪资构成和绩效情况_

【背景】：物流公司想要分析快递小哥的薪资构成和绩效情况，以便制定更合理的薪酬政策。

【原始表】：

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

输入

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;

SELECT c.courier_id, c.courier_name, (c.base_salary + COALESCE(d.total_delivery_fee, 0) - COALESCE(e.total_expense, 0)) AS total_income FROM couriers_info AS c LEFT JOIN ( -- 计算每个骑手的配送费总和 SELECT courier_id, SUM(delivery_fee) AS total_delivery_fee FROM deliveries_info WHERE DATE_FORMAT(delivery_date, '%Y-%m') = '2024-07' GROUP BY courier_id ) AS d ON c.courier_id = d.courier_id LEFT JOIN ( -- 计算每个骑手的支出总和 SELECT courier_id, SUM(expense_amount) AS total_expense FROM expenses_info WHERE DATE_FORMAT(expense_date, '%Y-%m') = '2024-07' GROUP BY courier_id ) AS e ON c.courier_id = e.courier_id ORDER BY c.courier_id;

SELECT c.courier_id, c.courier_name, (c.base_salary + COALESCE(SUM(d.delivery_fee), 0) - COALESCE(SUM(e.expense_amount), 0)) AS total_income FROM couriers_info AS c LEFT JOIN deliveries_info AS d ON c.courier_id = d.courier_id AND DATE_FORMAT(d.delivery_date, '%Y-%m') = '2024-07' LEFT JOIN expenses_info AS e ON c.courier_id = e.courier_id AND DATE_FORMAT(e.expense_date, '%Y-%m') = '2024-07' GROUP BY c.courier_id, c.courier_name ORDER BY c.courier_id;

物流公司想要分析快递小哥的薪资构成和绩效情况

输入

输出

问题信息

热门推荐

相关试题