优化复杂查询：使用临时表——案例：提取最新工资记录

建表和插入数据

# 创建员工数据表
CREATE TABLE test.employee_details (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(100)
);

# 创建薪水表
CREATE TABLE test.salary_records (
  record_id INT PRIMARY KEY,
  employee_id INT,
  salary_date DATE,
  amount DECIMAL(10, 2)
);

# 插入员工数据
INSERT INTO test.employee_details (employee_id, name, department) VALUES
(1, 'Alice', 'Finance'),
(2, 'Bob', 'HR'),
(3, 'Charlie', 'IT');

# 插入薪水数据
INSERT INTO test.salary_records (record_id, employee_id, salary_date, amount) VALUES
(1, 1, '2023-01-15', 3000.00),
(2, 1, '2023-02-15', 3100.00),
(3, 2, '2023-01-20', 2800.00),
(4, 2, '2023-02-20', 2900.00),
(5, 3, '2023-01-25', 3200.00),
(6, 3, '2023-02-25', 3300.00);

现在，我们有了两个数据表employee_details和salary_records，以及一些示例数据。每个员工都有两条工资记录，每条记录代表一个月的工资。

案例查询：提取最新工资记录

最后，我们展示了一个实用的案例查询，用于获取每个员工的最新工资记录。通过使用SQL查询，我们可以方便地提取每个员工最近一次的薪酬信息。

with latest_salaries as (
  -- 从 test.salary_records 表格中选择以下列
  select
    employee_id,                  -- 员工编号列
    max(salary_date) as latest_date  -- 最大工资日期列，并将其命名为 latest_date
  from test.salary_records         -- 选择的表格是 test.salary_records
  -- 按照员工编号分组，以获取每个员工的最新工资日期
  group by employee_id
)

select
  e.employee_id,
  e.name,
  e.department,
  s.salary_date as latest_salary_date,
  s.amount as latest_salary_amount
from test.employee_details e
inner join test.salary_records s on e.employee_id = s.employee_id
inner join latest_salaries ls on e.employee_id = ls.employee_id and s.salary_date = ls.latest_date
order by e.employee_id;

更好的写法，全部直接使用临时表，会高效很多：

with latest_salaries as (
  -- 从 test.salary_records 表格中选择以下列
  select
    employee_id,                  -- 员工编号列
    max(salary_date) as latest_date  -- 最大工资日期列，并将其命名为 latest_date
  from test.salary_records         -- 选择的表格是 test.salary_records
  -- 按照员工编号分组，以获取每个员工的最新工资日期
  group by employee_id
),
employee as (
    select
        employee_id,
        name,
        department
    from test.employee_details
),
salary as (
    select
        employee_id,
        salary_date as latest_salary_date,
        amount as latest_salary_amount
    from test.salary_records
)
select
    e.employee_id,
    e.name,
    e.department,
    s.latest_salary_date,
    s.latest_salary_amount

from employee e
inner join salary s on e.employee_id = s.employee_id
inner join latest_salaries ls on e.employee_id = ls.employee_id and s.latest_salary_date = ls.latest_date
order by e.employee_id;

通过这样的数据表设计和查询，企业能够高效地管理员工信息和薪酬数据，从而实现更精准的人力资源管理和财务规划。