根据一列从表中删除重复行

我创建了一个包含 4 个表的多个内部联接的表，但结果返回了重复的记录。这是我正在使用的代码

SELECT   tblLoadStop.LoadID,
         tblCustomer.CustomerID,
         tblLoadMaster.BillingID,
         tblLoadMaster.LoadID,
         tblLoadMaster.PayBetween1,
         LoadStopID,
         tblLoadMaster.Paybetween2,
         tblStopLocation.StopLocationID,
         tblStopLocation.city,
         tblStopLocation.state,
         tblStopLocation.zipcode,
         tblLoadSpecifications.LoadID,
         tblLoadSpecifications.LoadSpecificationID,
         Picks,
         Stops,
         Typeofshipment,
         Weight,
         LoadSpecClass,
         Miles,
         CommodityList,
         OriginationCity,
         OriginationState,
         DestinationCity,
         DestinationState,
         LoadRate,
         Status,
         CompanyName,
         Customerflag,
         tblCustomer.CustomerID,
         tblCustomer.AddressLine1,
         tblCustomer.City,
         tblCustomer.State,
         tblCustomer.Zipcode,
         CompanyPhoneNumber,
         CompanyFaxNumber,
         SCAC,
         tblLoadMaster.Salesperson,
         Change,
         StopType
FROM     tblLoadMaster
            INNER JOIN tblLoadSpecifications 
                  ON tblLoadSpecifications.LoadID = tblLoadMaster.LoadID
            INNER JOIN tblLoadStop
                   ON tblLoadStop.LoadID = tblLoadMaster.LoadID
            INNER JOIN tblStopLocation
                   ON tblStopLocation.StopLocationID = tblLoadStop.StopLocationID
            INNER JOIN tblCustomer
                   ON tblCustomer.CustomerID = tblLoadMaster.CustomerID
WHERE    tblLoadMaster.Phase LIKE '%2%'
ORDER BY tblLoadMaster.LoadID DESC;

这是我得到的结果

Load ID   Customer   Salesperson     Origin  Destination     Rate    
-------------------------------------------------------------------------
13356     FedEx           Alex           Duluth    New York     300
13356     FedEx           Steve          Florida   Kansas       400

我只想显示第一行，

13356     FedEx           Alex           Duluth    New York     300

并删除底行，

13356     FedEx           Steve          Florida   Kansas       400

tblLoadStop 表具有来自 tblloadMaster 表的重复 LoadID 的重复记录

如果您使用的是 SQL Server 2005 及更高版本（您在这方面不够具体），一种方法是使用 CTE（通用表表达式）。

使用此 CTE，您可以按照某些标准对数据进行分区 - 即您的LoadID- 让 SQL Server 对每个“分区”的所有行进行编号，从 1 开始，按某些条件排序（您不太清楚how您决定在问题中保留哪一行以及忽略哪一行）。

所以尝试这样的事情：

;WITH CTE AS
(
   SELECT 
       LoadID, Customer, Salesperson, Origin, Destination, Rate,
       RowNum = ROW_NUMBER() OVER(PARTITION BY LoadID ORDER BY tblLoadstopID ASC) 
   FROM 
       dbo.tblLoadMaster lm
     ......
   WHERE
      lm.Phase LIKE '%2%'
)
SELECT 
   LoadID, Customer, Salesperson, Origin, Destination, Rate
FROM 
   CTE
WHERE
   RowNum = 1

在这里，我仅选择每个“分区”的“第一个”条目（即每个LoadId) - 按某些标准排序 (updated:订购依据tblLoadstopID- 正如您提到的）您需要在 CTE 中定义。

这接近你正在寻找的东西吗？