基于一列从表中删除重复行

本文关键字：删除 于一列 | 更新日期: 2023-09-27 17:50:52

我从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表的重复的lodid记录

基于一列从表中删除重复行

一种方法是使用CTE(公共表表达式)，如果你是在SQL Server 2005和更新的(你在这方面没有足够的具体)。

有了这个CTE，你可以按一些标准分区你的数据-即你的LoadID -并有SQL Server编号你所有的行从1开始为每个"分区"，按一些标准排序(你不是很清楚如何你决定哪一行保留，哪些忽略在你的问题)。

那么试试这样写:

;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)的"第一个"条目-按照您需要在CTE中定义的一些标准(更新:由tblLoadstopID排序-如您所提到的)排序。

这接近你想要的吗??

相关文章：