SQL Server 2000无ROW_NUMBER函数，如何为空闲线路分配员工？

嘿，这个问题我太熟悉了！SQL Server 2000没有ROW_NUMBER()这类窗口函数，确实得用点老派的技巧来实现分组匹配。咱们一步步来搞定：

核心思路 #

因为没法直接用窗口函数给每个国家/地区组内的线路和员工编号，我们可以用自连接计数的方式模拟分组序号，然后通过序号把线路和员工一一对应起来。

假设你的表结构（如果和实际有出入，调整字段名即可） #

• 表A（可用线路）：Country（国家）、Region（地区）、LineID（线路编号）
• 表B（可分配员工）：Country（国家）、Region（地区）、EmployeeID（员工编号）

步骤1：给每个国家/地区的线路生成组内序号 #

通过自连接子查询计数，模拟ROW_NUMBER() OVER (PARTITION BY Country, Region ORDER BY LineID)的效果：

SELECT 
    Country,
    Region,
    LineID,
    -- 统计当前组内比当前LineID小（或等于）的记录数，得到组内序号
    (SELECT COUNT(*) 
     FROM TableA a2 
     WHERE a2.Country = a1.Country 
       AND a2.Region = a1.Region 
       AND a2.LineID <= a1.LineID) AS LineSeq
FROM TableA a1

步骤2：给每个国家/地区的员工生成组内序号 #

同样用自连接计数给员工分组编号：

SELECT 
    Country,
    Region,
    EmployeeID,
    -- 统计当前组内比当前EmployeeID小（或等于）的记录数，得到组内序号
    (SELECT COUNT(*) 
     FROM TableB b2 
     WHERE b2.Country = b1.Country 
       AND b2.Region = b1.Region 
       AND b2.EmployeeID <= b1.EmployeeID) AS EmpSeq
FROM TableB b1

步骤3：通过序号匹配生成表C #

把上面两个结果集按Country、Region和序号（LineSeq = EmpSeq）连接，就能得到每个线路对应单个员工的结果：

SELECT 
    a.Country,
    a.Region,
    a.LineID,
    b.EmployeeID
INTO TableC -- 如果需要直接生成表C，用INTO子句；否则直接查询结果
FROM (
    SELECT 
        Country,
        Region,
        LineID,
        (SELECT COUNT(*) 
         FROM TableA a2 
         WHERE a2.Country = a1.Country 
           AND a2.Region = a1.Region 
           AND a2.LineID <= a1.LineID) AS LineSeq
    FROM TableA a1
) a
JOIN (
    SELECT 
        Country,
        Region,
        EmployeeID,
        (SELECT COUNT(*) 
         FROM TableB b2 
         WHERE b2.Country = b1.Country 
           AND b2.Region = b1.Region 
           AND b2.EmployeeID <= b1.EmployeeID) AS EmpSeq
    FROM TableB b1
) b ON a.Country = b.Country 
   AND a.Region = b.Region 
   AND a.LineSeq = b.EmpSeq

注意事项 #

• 如果LineID或EmployeeID不是适合排序的字段（比如是无序字符串），可以换成其他排序字段（比如创建时间CreateDate），只要保证组内的序号是唯一且有序的就行。
• 如果某个国家/地区的线路数多于员工数，用LEFT JOIN可以保留所有线路（但超出员工数量的线路会没有员工匹配）；如果员工数多于线路数，用RIGHT JOIN可以保留所有员工（但超出线路数量的员工会没有线路匹配），根据你的实际需求调整连接类型。
• 这种方法的性能取决于数据量，如果数据特别大，可能需要考虑加索引优化自连接的查询效率。

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