企业官网建设流程全解析

驻马店网站seo,做网站需要多大的显存,做纸贸易的好网站,ui设计案例欣赏分析描述 【背景】#xff1a;物流公司想要分析快递小哥的薪资构成和绩效情况#xff0c;以便制定更合理的薪酬政策。 【原始表】#xff1a; couriers_info #xff08;快递员#xff09;表: courier_id (快递员 ID): 快递员的唯一标识符#xff0c;INTcourier_name (快…描述【背景】物流公司想要分析快递小哥的薪资构成和绩效情况以便制定更合理的薪酬政策。【原始表】couriers_info 快递员表:courier_id (快递员 ID): 快递员的唯一标识符INTcourier_name (快递员姓名): 快递员的姓名VARCHAR(50)base_salary (基本工资): 快递员的基本工资DECIMAL(10, 2)deliveries_info 派送表:delivery_id (派送 ID): 派送任务的唯一标识符INTcourier_id (快递员 ID): 关联快递员表的快递员 IDINTdelivery_date (派送日期): 派送的日期DATEdelivery_fee (派送费用): 每次派送的费用DECIMAL(10, 2)expenses_info 支出表expense_id支出条目的唯一标识符INTcourier_id与 couriers_info 表中的 courier_id 相关联快递员的唯一标识符INTexpense_date支出发生的日期DATEexpense_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 5050 2100支出是3020 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.00with 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.t1couriers_info.base_salary-t2.t2,2) as total_income from t2 join t1 on t1.courier_idt2.courier_id join couriers_info on t1.courier_id couriers_info.courier_id order by courier_id