MySQL复杂查询异常：无法关联员工最新记录与考勤工时表数据

Got it, let's work through this problem together. You said you’ve already nailed getting the latest record per employee from a single table, but you’re stuck pulling in data from the employees and employee_payrollhours tables. Let’s break this down and fix the join issue.

First, let's align on the core goal:

• Grab the most recent entry per employee from your primary table (I’ll call it employee_core for clarity since you didn’t name it)
• Join this with employees (for employee details) and employee_payrollhours (which has multiple entries per employee)

Step 1: Lock in Your "Latest Record" Base Query #

I’m assuming you used a window function like ROW_NUMBER() to isolate the latest record (the standard approach here). Let’s formalize that as a CTE (Common Table Expression) to make joining easier:

WITH latest_employee_entries AS (
    SELECT 
        *,
        -- Assign a row number per employee, ordered by recency
        ROW_NUMBER() OVER (PARTITION BY employee_number ORDER BY record_timestamp DESC) AS row_rank
    FROM employee_core -- Replace with your actual primary table name
)
-- Filter to only keep the top (latest) entry per employee
SELECT * FROM latest_employee_entries WHERE row_rank = 1;

Step 2: Join with employees and employee_payrollhours #

Since employee_payrollhours has multiple rows per employee, we’ll use a LEFT JOIN (swap to INNER JOIN if you only want employees with existing payroll hours) to retain all latest employee records while pulling in their related payroll data.

Here’s the full working query:

WITH latest_employee_entries AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY employee_number ORDER BY record_timestamp DESC) AS row_rank
    FROM employee_core -- Replace with your actual primary table name
)
SELECT
    -- Columns from your latest employee record
    lee.employee_number, lee.status, lee.record_timestamp,
    -- Columns from the employees table (customize to your needs)
    e.first_name, e.last_name, e.department, e.hire_date,
    -- Columns from the payroll hours table (customize to your needs)
    eph.pay_period_start, eph.pay_period_end, eph.hours_worked, eph.overtime_hours
FROM latest_employee_entries lee
-- Join to get employee details (use INNER JOIN if you only want employees with a matching entry)
INNER JOIN employees e 
    ON lee.employee_number = e.employee_number
-- Join to get all payroll hours for the employee
LEFT JOIN employee_payrollhours eph 
    ON lee.employee_number = eph.employee_number
WHERE lee.row_rank = 1;

Common Fixes if You’re Still Not Getting Joined Data #

If the query isn’t returning data from employees or employee_payrollhours, check these quick wins:

• Matching Column Types: Ensure employee_number is the same data type across all three tables (e.g., INT vs VARCHAR can cause silent join failures)
• Join Logic: If you used INNER JOIN for employee_payrollhours, you’ll only see employees with existing payroll entries. Switch to LEFT JOIN to include employees even if they have no payroll hours yet.
• Recency Order: Double-check that your ORDER BY in the window function uses the correct column (e.g., updated_at instead of created_at if records can be edited after creation)
• Data Existence: Verify there are matching records in the joined tables! Run a quick test like SELECT * FROM employees WHERE employee_number = '123' to confirm the employee ID exists there.

Example with Sample Data #

Let’s say your employee_core table has:

employees table:

employee_payrollhours table:

Running the query above would return:

Perfect—each employee’s latest record is paired with all their payroll hours entries.

内容的提问来源于stack exchange，提问作者Jim Baize