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

bejyjqdl  于 2022-10-22  发布在  SQL Server
关注(0)|答案(5)|浏览(232)

我想列出所有拥有最新电话号码和最新客户类型的客户。电话号码和客户类型定期更改,因此我只想获得最新记录,而不需要根据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

km0tfn4u

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
vulvrdjw

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
6psbrbz9

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标准的表数据历史记录功能,那么您将始终在主表中拥有最新的行,旧的行将保留在历史记录表中,并且可以通过时间点或日期间隔限制进行查询。

3wabscal

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;
8oomwypt

8oomwypt5#

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

相关问题