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;
通过以上MySQL语句,我们可以根据不同的计算公式,计算出员工的实际工资。在实际工作中,我们可以进一步优化这些语句,提高计算效率和精度。