模拟sql Server 8.00.760版本的sql Server ROW_COUNT()

Question

我需要使用ROW_NUMBER((函数进行查询，以便根据PurchaseID和交货日期枚举所有采购订单行订单：

SELECT
PL.PURCHID,
PL.LINENUMBER,
ROW_NUMBER() OVER (PARTITION BY PL.PURCHID
ORDER BY PL.LineNumber, PL.DELIVERYDATE DESC) AS LineNumberOrdered
FROM AXDB13_R1.dbo.PURCHLINE PL
WHERE PL.DATAAREAID = 'pol';

问题是数据库产品太旧，无法识别该功能。我该如何效仿呢？

非常感谢你的帮助！

Answer 1

我们可以将您对ROW_NUMBER的调用替换为一个相关的计数查询，该查询生成以前PURCHID:的每个分区的计数

SELECT
PL.PURCHID,
PL.LINENUMBER,
(SELECT COUNT(*)
FROM AXDB13_R1.dbo.PURCHLINE PL2
WHERE PL2.PURCHID = PL.PURCHID AND
(PL2.LineNumber < PL.LineNumber OR
PL2.LineNumber = PL.LineNumber AND
PL2.DELIVERYDATE >= PL.DELIVERYDATE)) AS LineNumberOrdered
FROM AXDB13_R1.dbo.PURCHLINE PL
WHERE
PL.DATAAREAID = 'pol';