在SQL Server2008中运行Access2003的相同SQL时发生SQL错误

Question

我有以下用Access 2003编写的SQL。现在，我必须在SQL Server2008上运行SQL时发生SQL Server2008和 发生错误，因为Access的First（）函数无法在SQL Server 2008中识别。 在SQL Server 2008中，我尝试了使用TOP（）函数，但我仍然无法解决它。请帮帮我。

SELECT DISTINCTROW TableA.TCode,     
    TableA.DCode,     
    TableA.DepCode,     
    TableA.ShouhinCode,     
    First(TableB.TName) AS TNameFirstRecord,     
    First(TableC.LDepName) AS LDepNameFirstRecord,      
    First(TableD.ThingType) AS ThingTypeFirstRecord,     
    First(TableA.GoodsName) AS GoodsNameFirstRecord,     
    Sum(TableA.Amount) AS AmountData,     
    Sum(TableA.MoneyAmount) AS MoneyAmountData,     
    Sum(TableA.DetailedMoneyAmount) AS DetailedMoneyAmounData,      
    Sum(TableA.SummaryMoneyAmount) AS SummaryMoneyAmountData,     
    TableA.POSNo     
FROM (     
(TableA INNER JOIN TableC ON TableA.DCode = TableC.DCode)     
INNER JOIN TableB ON TableA.TCode = TableB.TCode     
)     
    INNER JOIN TableD ON TableA.DepCode = TableD.DepCode     
GROUP BY TableA.TCode,     
    TableA.DCode,     
    TableA.DepCode,     
    TableA.ShouhinCode,     
    TableA.POSNo     
ORDER BY TableA.TCode,     
    TableA.DCode,     
    TableA.DepCode,     
    TableA.ShouhinCode      

Answer 1

我认为你需要使用一个Windowing Function

您与对他们的行号版本替换你的表

SELECT TableA.TCode,     
    A.DCode,     
    A.DepCode,     
    A.ShouhinCode,     
    B.TName AS TNameFirstRecord,     
    C.LDepNamee AS LDepNameFirstRecord,      
    D.ThingType AS ThingTypeFirstRecord,     
    A.GoodsNameAS GoodsNameFirstRecord,     
    Sum(A.Amount) AS AmountData,     
    Sum(A.MoneyAmount) AS MoneyAmountData,     
    Sum(A.DetailedMoneyAmount) AS DetailedMoneyAmounData,      
    Sum(A.SummaryMoneyAmount) AS SummaryMoneyAmountData,     
    A.POSNo     
FROM 
    INNER JOIN (select row_number() over (partition by object_id ORDER BY Name DESC) as RowNumber, * from TableA) A     
    INNER JOIN (select row_number() over (partition by object_id ORDER BY Name DESC) as RowNumber, * from TableC) C ON A.DCode = C.DCode     
    INNER JOIN (select row_number() over (partition by object_id ORDER BY Name DESC) as RowNumber, * from TableB) B ON A.TCode = B.TCode     
    INNER JOIN (select row_number() over (partition by object_id ORDER BY Name DESC) as RowNumber, * from TableD) D ON A.DepCode = D.DepCode  

WHERE 
    A.RowNumber = 1 AND B.RowNumber = 1 AND C.RowNumber = 1 AND D.RowNumber = 1 

GROUP BY A.TCode,     
    A.DCode,     
    A.DepCode,     
    A.ShouhinCode,     
    A.POSNo     
ORDER BY A.TCode,     
    A.DCode,     
    A.DepCode,     
    A.ShouhinCode