根据一列从表中删除重复的行 [英] Removing Duplicate row from a table based on one column

问题描述

我创建的表有多个内从4表的连接，但结果带回重复的记录。在这里，code，我现在用

I created a table with multiple inner joins from 4 tables but the results brings back duplicate records. Here code that I am using

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

我只希望在第一行显示，

I only want the first row to show,

13356     FedEx           Alex           Duluth    New York     300

和去除最下面一排，

13356     FedEx           Steve          Florida   Kansas       400

该tblLoadStop表中有重复记录与tblloadMaster表重复LoadID

The tblLoadStop Table has the duplicate record with a duplicate LoadID from tblloadMaster Table

推荐答案

一种方法是使用一个CTE（公共表前pression）如果你的SQL Server 2005及更高版本（你是不是具体够在这方面）。

One approach would be to use a CTE (Common Table Expression) if you're on SQL Server 2005 and newer (you aren't specific enough in that regard).

通过这个CTE，可以通过某种标准划分您的数据 - 即你的 LoadID - 并启动SQL Server的一些所有的行以1为每个分区有些标准命令（你是不是很清楚如何您决定保留这行，这样在你的问题忽略）。

With this CTE, you can partition your data by some criteria - i.e. your LoadID - and have SQL Server number all your rows starting at 1 for each of those "partitions", ordered by some criteria (you're not very clear on how you decide which row to keep and which to ignore in your question).

因此​​，尝试这样的：

So try something like this:

;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 ） - 由某些标准（有序​​的更新：的顺序由 tblLoadstopID - 正如你所提到），你需要在你的CTE定义

Here, I am selecting only the "first" entry for each "partition" (i.e. for each LoadId) - ordered by some criteria (updated: order by tblLoadstopID - as you mentioned) you need to define in your CTE.

这是否方法，你在找什么？

Does that approach what you're looking for??

这篇关于根据一列从表中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！