SQL Server 查询返回重复行

Question

我有一个 SQL 查询，它是多个表的连接，它返回重复的行，经过数小时后无法找出问题所在

SELECT 
    StkItem.iUOMStockingUnitID, 
    _etblUnits1.cUnitCode as 'parkSize', 
    _etblUnits2.cUnitCode as 'quantitySize', 
    InvNum.fInvTotExclForeign, 
    [_btblInvoiceLines].*, 
    [_rtblCountry].cCountryName, 
    [CurrencyHist].fBuyRate, 
    Vendor.Name, 
    InvNum.OrderDate, 
    InvNum.InvNumber 
FROM
    [dbo].[_btblInvoiceLines] 
LEFT JOIN 
    StkItem ON StkItem.StockLink = [_btblInvoiceLines].iStockCodeID 
LEFT JOIN 
    _etblUnits as _etblUnits1 ON _etblUnits1.idunits = StkItem.iUOMDefSellUnitID 
LEFT JOIN 
    _etblUnits as _etblUnits2 ON _etblUnits2.idunits = StkItem.iUOMStockingUnitID 
LEFT JOIN 
    InvNum ON iInvoiceID = AutoIndex 
LEFT JOIN 
    Vendor ON Vendor.DCLink = InvNum.AccountID 
LEFT JOIN 
    [_rtblCountry] ON [_rtblCountry].idCountry = Vendor.iCountryID   
LEFT JOIN 
    [CurrencyHist] ON InvNum.ForeignCurrencyID = [CurrencyHist].iCurrencyID 
WHERE 
    OrderNum = '' 
    AND [CurrencyHist].iCurrencyID = (SELECT TOP 1 iCurrencyID 
                                      FROM [CurrencyHist] 
                                      WHERE iCurrencyID = InvNum.ForeignCurrencyID 
                                      ORDER BY idCurrencyHist DESC)

这里是查询，任何帮助将不胜感激，提前致谢

Answer 1

根据您之前的评论，当您加入 [CurrencyHist] 时，问题就来了。从名称上看，它似乎是一个历史表，因此必须有多行作为每种货币的历史记录。要消除重复行，您应该加入特定货币的最新更新记录。因此，您的查询可能如下所示，

    SELECT StkItem.iUOMStockingUnitID, 
          _etblUnits1.cUnitCode as 'parkSize', 
          _etblUnits2.cUnitCode as 'quantitySize', 
          InvNum.fInvTotExclForeign, 
          [_btblInvoiceLines].*, 
          [_rtblCountry].cCountryName, 
          [CurrencyHist].fBuyRate, 
          Vendor.Name, 
          InvNum.OrderDate, 
          InvNum.InvNumber 
    FROM [dbo].[_btblInvoiceLines] 
    LEFT JOIN StkItem ON StkItem.StockLink = [_btblInvoiceLines].iStockCodeID 
    LEFT JOIN _etblUnits as _etblUnits1 ON _etblUnits1.idunits = StkItem.iUOMDefSellUnitID 
    LEFT JOIN _etblUnits as _etblUnits2 ON _etblUnits2.idunits = StkItem.iUOMStockingUnitID 
    LEFT JOIN InvNum ON iInvoiceID = AutoIndex 
    LEFT JOIN Vendor ON Vendor.DCLink = InvNum.AccountID 
    LEFT JOIN [_rtblCountry] ON [_rtblCountry].idCountry = Vendor.iCountryID   
    LEFT JOIN (SELECT  DENSE_RANK() over (partition by  [CurrencyHist].iCurrencyID  order by [CurrencyHist].LastUpdated desc) as rn,[CurrencyHist].iCurrencyID as 'iCurrencyID'
    FROM [CurrencyHist] AS [CurrencyHist] 
    )[CurrencyHist] ON InvNum.ForeignCurrencyID = [CurrencyHist].iCurrencyID 
    and [CurrencyHist].rn=1
WHERE OrderNum = '' AND 
[CurrencyHist].iCurrencyID = (SELECT TOP 1 iCurrencyID 
                                        FROM [CurrencyHist] 
                                        WHERE iCurrencyID = InvNum.ForeignCurrencyID 
                                        ORDER BY idCurrencyHist DESC)

注意:我假设 CurrencyHist 表有一个 LastUpdated 和 DateTime 数据类型 Column