SQL Server 8.00.760中模拟ROW_NUMBER()实现采购订单行编号

Got it, since SQL Server 2000 (version 8.00.760) doesn't support window functions like ROW_NUMBER(), we can replicate your required logic using correlated subqueries or self-joins with counting—both methods work by calculating the position of each row within its PURCHID partition based on your sort criteria.

Option 1: Correlated Subquery (Most Intuitive) #

This approach counts how many rows in the same PURCHID group come before the current row (using your LINENUMBER + DELIVERYDATE DESC sort order), then adds 1 to start numbering from 1 (matching ROW_NUMBER() behavior):

SELECT 
    PL.PURCHID, 
    PL.LINENUMBER,
    (
        SELECT COUNT(*) 
        FROM AXDB13_R1.dbo.PURCHLINE PL2
        WHERE PL2.DATAAREAID = 'pol'
          AND PL2.PURCHID = PL.PURCHID
          AND (
              -- Rows with smaller LINENUMBER come first
              PL2.LINENUMBER < PL.LINENUMBER
              -- Same LINENUMBER? Order by DELIVERYDATE descending
              OR (PL2.LINENUMBER = PL.LINENUMBER AND PL2.DELIVERYDATE > PL.DELIVERYDATE)
              -- Handle ties: if LINENUMBER and DELIVERYDATE are identical, use a unique column (like RECID) to break ties
              OR (PL2.LINENUMBER = PL.LINENUMBER AND PL2.DELIVERYDATE = PL.DELIVERYDATE AND PL2.RECID < PL.RECID)
          )
    ) + 1 AS LineNumberOrdered
FROM AXDB13_R1.dbo.PURCHLINE PL 
WHERE PL.DATAAREAID = 'pol';

Option 2: Self-Join with GROUP BY #

If you prefer a join-based approach, this works similarly by joining the table to itself and counting matching rows:

SELECT 
    PL.PURCHID, 
    PL.LINENUMBER,
    COUNT(PL2.RECID) + 1 AS LineNumberOrdered
FROM AXDB13_R1.dbo.PURCHLINE PL
LEFT JOIN AXDB13_R1.dbo.PURCHLINE PL2
    ON PL2.DATAAREAID = 'pol'
    AND PL2.PURCHID = PL.PURCHID
    AND (
        PL2.LINENUMBER < PL.LINENUMBER
        OR (PL2.LINENUMBER = PL.LINENUMBER AND PL2.DELIVERYDATE > PL.DELIVERYDATE)
        OR (PL2.LINENUMBER = PL.LINENUMBER AND PL2.DELIVERYDATE = PL.DELIVERYDATE AND PL2.RECID < PL.RECID)
    )
WHERE PL.DATAAREAID = 'pol'
GROUP BY PL.PURCHID, PL.LINENUMBER, PL.DELIVERYDATE, PL.RECID
ORDER BY PL.PURCHID, LineNumberOrdered;

Key Notes for Performance #

SQL Server 2000 doesn't have the query optimizations of newer versions, so to avoid slow queries on large datasets:

• Create a composite index on (DATAAREAID, PURCHID, LINENUMBER, DELIVERYDATE DESC, RECID) to speed up the subquery/join filtering and sorting.

  CREATE NONCLUSTERED INDEX IX_PURCHLINE_PartitionSort ON AXDB13_R1.dbo.PURCHLINE 
  (DATAAREAID, PURCHID, LINENUMBER, DELIVERYDATE DESC, RECID);
  

• The RECID check is optional but recommended—if you have rows with identical PURCHID, LINENUMBER, and DELIVERYDATE, it ensures each row gets a unique LineNumberOrdered (just like ROW_NUMBER() does).

内容的提问来源于stack exchange，提问作者Nestor C.