MS SQL Server 2008两个表中的不同的一列 [英] Distinct one column in two tables MS SQL Server 2008

问题描述

我写了这个查询但是我缺少一些行，因此我只想使用CustomerID，并获得最新的记录，因为我有table1中的RecordUpdate_date列。 （记录）和获取重复记录。

请帮助我。
感谢

表1：

  CustomerID，CustomerName， UpdateDate 
  

表2：

  CustomerID，DateofBirth 
  

我的查询：

  SELECT a.CustomerID 
，a.CustomerName 
，a.RecordUpDate_date 
，b.DateofBirth 
 FROM Table1 AS a 
 INNER JOIN（
 SELECT CustomerID 
，MAX（RecordUpdate_date）AS max_RecordUpdate_date 
 FROM Table1 
 GROUP BY CustomerID 
）AS abc 
 ON abc.CustomerID = a.CustomerID 
 AND abc.max_RecordUpdate_date = a.RecordUpdate_date 
 INNER JOIN Table2 AS b 
 ON b.CustomerID = a.CustomerID 
 INNER JOIN（
 SELECT CustomerID 
，MAX（DateofBirth）AS max_dob 
 FROM table2 
 GROUP BY CustomerID 
）AS m 
 ON m.CustomerID = a.Customer 
 AND m.max_cus = c.DateofBirth 
  

解决方案

方式获得最新的记录。我一直使用的个人资料是使用 ROW_NUMBER（） http://msdn.microsoft.com/en-us/library/ms186734.aspx 。

首先在CTE中，我们基于customerid和desc date字段对行进行编号。接下来，您选择 rn = 1 这只会为每个客户获取最新记录。

 ; WITH CTE 
 AS（
 SELECT customerID 
，customerName 
，UpdateDate 
，ROW_NUMBER（）OVER（PARTITION BY customerID ORDER BY UpdateDate DESC）AS rn 
 FROM table1 AS a 
）
 SELECT a.customerID 
，a.CustomerName 
，a.UpdateDate 
，b.DateOfBrith 
 FROM CTE a 
 JOIN table2 AS b 
 ON a.customerId = b.CustomerID 
其中a.rn = 1 
  

I want to apply distinct on only CustomerID and get the latest record as I have RecordUpdate_date column in my table1.

I wrote this query but I am missing some rows(records) and getting duplicate records.

Please help me with that. Thanks

Table1:

CustomerID, CustomerName, UpdateDate

Table2:

CustomerID, DateofBirth

My Query:

SELECT a.CustomerID
       ,a.CustomerName
       ,a.RecordUpDate_date
       ,b.DateofBirth
    FROM Table1 AS a
    INNER JOIN (
                 SELECT CustomerID
                       ,MAX(RecordUpdate_date) AS max_RecordUpdate_date
                    FROM Table1
                    GROUP BY CustomerID
               ) AS abc
        ON abc.CustomerID = a.CustomerID
           AND abc.max_RecordUpdate_date = a.RecordUpdate_date
    INNER JOIN Table2 AS b
        ON b.CustomerID =  a.CustomerID
    INNER JOIN (
                 SELECT CustomerID
                       ,MAX(DateofBirth) AS max_dob
                    FROM table2
                    GROUP BY CustomerID
               ) AS m
        ON m.CustomerID = a.Customer
           AND m.max_cus = c.DateofBirth

解决方案

There are many ways to get only latest record. My personal that I use all the time is to use ROW_NUMBER() http://msdn.microsoft.com/en-us/library/ms186734.aspx.

First in CTE we number rows based on the customerid and desc date field. Next you select where rn =1 this gets only latest record for each customer.

;WITH   CTE
          AS (
               SELECT customerID
                   ,customerName
                   ,UpdateDate
                   ,ROW_NUMBER() OVER ( PARTITION BY customerID ORDER BY UpdateDate DESC ) AS rn
                FROM table1 AS a
             )
    SELECT a.customerID
           ,a.CustomerName
           ,a.UpdateDate
           ,b.DateOfBrith
        FROM CTE a
        JOIN table2 AS b
            ON a.customerId = b.CustomerID
        where a.rn = 1

这篇关于MS SQL Server 2008两个表中的不同的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！