SQL查询：按员工分组、按项目起始日期排序生成每N个项目的批次号

Hey there! Let's figure out how to solve this batch numbering problem for your employee projects. This is a classic window function use case, and it's pretty straightforward once you break down the logic.

Core Logic #

The key steps to get this right are:

• Group projects by EmployeeID so each employee's projects are handled independently
• Sort each employee's projects by ProjectStartDate to maintain chronological order
• Assign a sequential row number to each project within the employee's group
• Calculate the batch number by grouping every 5 sequential projects together

General SQL Solution (PostgreSQL, SQL Server, Oracle, etc.) #

Assuming your table is named EmployeeProjects with columns like EmployeeID, ProjectID, ProjectStartDate, and ProjectName, here's the query:

SELECT
    EmployeeID,
    ProjectID,
    ProjectStartDate,
    ProjectName,
    -- Calculate batch number: group every 5 projects
    ((ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ProjectStartDate) - 1) / 5) + 1 AS BatchNumber
FROM
    EmployeeProjects
ORDER BY
    EmployeeID,
    BatchNumber,
    ProjectStartDate;

How This Works #

• PARTITION BY EmployeeID: Ensures we only calculate row numbers within each individual employee's project set (so one employee's row numbers don't interfere with another's)
• ORDER BY ProjectStartDate: Makes sure projects are ordered chronologically before assigning batches—so batches are based on project start order
• (ROW_NUMBER() - 1) / 5: Since ROW_NUMBER() starts at 1, subtracting 1 lets the first 5 projects return 0 when divided by 5 (using integer division). Adding 1 shifts the batch number to start at 1 instead of 0, which is more intuitive.

Adjustment for MySQL #

MySQL uses DIV for integer division (instead of / which returns a float), so modify the batch calculation like this:

SELECT
    EmployeeID,
    ProjectID,
    ProjectStartDate,
    ProjectName,
    ((ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY ProjectStartDate) - 1) DIV 5) + 1 AS BatchNumber
FROM
    EmployeeProjects
ORDER BY
    EmployeeID,
    BatchNumber,
    ProjectStartDate;

Example Output #

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