如何仅使用SQL SERVER中的最新记录连接表

bejyjqdl 于 2022-10-22 发布在 SQL Server

关注(0)|答案(5)|浏览(234)

我想列出所有拥有最新电话号码和最新客户类型的客户。电话号码和客户类型定期更改，因此我只想获得最新记录，而不需要根据lastestupdate列获取旧值
客户：

+------------+--------------------+------------+
|latestUpdate| CustID |  AddID    |  TypeID    |
+------------+--------+-----------+-------------
| 2020-03-01 |      1 |    1      |     1     |
| 2020-04-07 |      2 |    2      |     2     |
| 2020-06-13 |      3 |    3      |     3     |
| 2020-03-29 |      4 |    4      |     4     |
| 2020-02-06 |      5 |    5      |     5     |
+------------+--------+------------+----------+

客户地址：

+------------+--------+-----------+
|latestUpdate| AddID  | Mobile    |
+------------+--------+-----------+
| 2020-03-01 |      1 | 66666     |
| 2020-04-07 |      1 | 55555     |
| 2020-06-13 |      2 | 99999     |
| 2020-03-29 |      3 | 11111     |
| 2020-02-06 |      3 | 22222     |
+------------+--------+-----------+

客户类型：

+------------+--------+-----------+
|latestUpdate| TypeId | TypeName  |
+------------+--------+-----------+
| 2020-03-01 |      1 |  First    |
| 2020-04-07 |      1 | Second    |
| 2020-06-13 |      3 | Third     |
| 2020-03-29 |      4 | Fourth    |
| 2020-02-06 |      5 | Fifth     |
+------------+--------+-----------+

当我尝试加入时，我总是得到重复的customerID，而不仅仅是最新的记录
我想展示客户。CustID和CustomerType。TypeName和CustomerAddress.Mobile

sql-server

来源：https://stackoverflow.com/questions/74136365/how-to-join-tables-only-with-the-latest-record-in-sql-server

5条答案

按热度按时间

km0tfn4u1#

您需要对最新客户类型和最新电话号码进行子查询，如下所示：

SELECT *
FROM (
    SELECT latestUpdate, CustID, AddID, TypeID,
        ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY latestUpdate DESC) AS RowNumber
    FROM Customer
) AS c
    INNER JOIN (
        SELECT latestUpdate, AddID, Mobile,
            ROW_NUMBER() OVER (PARTITION BY AddId ORDER BU ltestUpdate DESC) AS RowNumber
        FROM CustomerAddress
    ) AS t
        ON c.AddId = t.AddId
    INNER JOIN CustomerType ct
        ON ct.TypeId = c.TypeId
WHERE c.RowNumber = 1
    AND t.RowNumber = 1

赞(0）回复(0）举报 2022-10-22

vulvrdjw2#

比使用row_number更简单的方法是在有序子查询中使用m1n 1o1p和top 1：

select c.CustId, p.Mobile
from Customer c
  cross apply (
    select top 1 Mobile
    from CustomerAddress a
    where c.CustId = a.AddId
    order by a.latestUpdate
  ) p

赞(0）回复(0）举报 2022-10-22

6psbrbz93#

您需要使用一些子查询：

SELECT *
FROM   Customer AS C
       LETF OUTER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY LastestUpdate DESC) AS N
                        FROM   CustomerAddress) AS A
          ON C.CustID = A.CustID AND N = 1
       LETF OUTER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY LastestUpdate DESC) AS N
                        FROM   CustomerType) AS T
          ON C.CustID = T.CustID AND N = 1

如果您使用了Temporal表，这是ISO SQL标准的表数据历史记录功能，那么您将始终在主表中拥有最新的行，旧的行将保留在历史记录表中，并且可以通过时间点或日期间隔限制进行查询。

赞(0）回复(0）举报 2022-10-22

3wabscal4#

就是这样：

select * from (select *,RANK() OVER (
    PARTITION BY b.AddID    
    ORDER BY b.latestUpdate DESC,
) as rank1
from 
Customer a
left join
CustomerAddress b
on 
a.AddID=b.AddID        
left join
CustomerType c
on
v.TypeId =c.TypeId 
) where rank1=1;

赞(0）回复(0）举报 2022-10-22

8oomwypt5#

您应该使用“APPLY”运算符连接表。参见：Link

赞(0）回复(0）举报 2022-10-22