mysql计算员工实际工资语句

MySQL是一个功能强大且广泛使用的关系型数据库管理系统。在日常工作中,很多公司需要利用MySQL计算员工实际工资,下面是一些参考的MySQL语句。

-- 创建员工表 CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary FLOAT ); -- 插入员工数据 INSERT INTO employees VALUES (1, '张三', '市场部', 5000); INSERT INTO employees VALUES (2, '李四', '技术部', 8000); INSERT INTO employees VALUES (3, '王五', '人事部', 6000); INSERT INTO employees VALUES (4, '赵六', '财务部', 7000); -- 计算加班工资 SELECT id, name, department, salary + IFNULL(overtime_pay, 0) AS actual_salary FROM employees LEFT JOIN overtime ON employees.id = overtime.emp_id; -- 计算年终奖金 SELECT id, name, department, (salary + IFNULL(overtime_pay, 0)) * 1.5 AS actual_salary FROM employees LEFT JOIN overtime ON employees.id = overtime.emp_id; -- 根据绩效计算奖励 SELECT id, name, department, salary, CASE WHEN performance >80 THEN salary * 0.2 WHEN performance >60 THEN salary * 0.1 ELSE 0 END AS reward FROM employees;