TSQL-加入自己的表，仅选择最新行

Question

我有一个表 tbl_Partner （缩短），其中有两个“重复”合作伙伴：

RecordID (GUID) | RISEID (varchar(40)) | UpdateCounter(int) | etc. ...
-----------------------------------------------------------
12345678-9012-3456-7890-123456789012 | RISE123 | 0
87564321-9012-3456-7890-123456789012 | RISE123 | 1
-- other partners with different updatecounters etc.

现在我需要一个视图，其中包含每个合作伙伴的最新数据（最高的 UpdateCounter），但第一个 RecordID（最低的 UpdateCounter）

我尝试了这个SQL 已经：

select 
(select top 1 recordid from tbl_partner p2 where p2.riseid=p1.riseid order by p2.updatecounter ASC) as RecordID
, riseid, updatecounter
from tbl_partner p1
order by riseid

我得到：

RecordID (GUID) | RISEID (varchar(40)) | UpdateCounter(int)
-----------------------------------------------------------
12345678-9012-3456-7890-123456789012 | RISE123 | 0 --(old data)
12345678-9012-3456-7890-123456789012 | RISE123 | 1 --(new correct data with the first RecordID)
-- other rows/columns omitted

如何仅为每个 RecordID/RiseID 选择最新行？ （本例中 UpdateCounter=1）

郑重声明： UpdateCounter 当然可以是 1 以外的值，并且还有其他具有不同值的数据集。所以我不能使用简单的 WHERE updatecounter>0。

Answer 1

SELECT t1.RecordID,t1.RISEID,t2.UpdateCounter
FROM
   tbl_Partner t1
      inner join
   tbl_Partner t2
      on
          t1.RISEID = t2.RISEID
      left join
   tbl_Partner t1_anti
      on
          t1.RISEID = t1_anti.RISEID and
          t1_anti.UpdateCounter < t1.UpdateCounter
      left join
   tbl_Partner t2_anti
      on
          t1.RISEID = t2_anti.RISEID and
          t2_anti.UpdateCounter > t2.UpdateCounter
where
    t1_anti.RISEID is null and
    t2_anti.RISEID is null

目前尚不清楚您是否希望为只有单行具有特定 RISEID 的项目显示行。如果不是，请在 t1 和 t2 之间的连接中添加一个额外条件，以便 UpdateCounter 在 t2 上更高>。

两个外连接是为了确保不存在早于 t1 (t1_anti) 的行，也不存在晚于 t2 (t2_anti ）。在 WHERE 子句中，我们确保这些连接不成功。

---

另一种方法，使用稍微现代的风格：

;With OrderedRows as (
    Select RecordID,RISEID,UpdateCounter,
      ROW_NUMBER() OVER (PARTITION BY RISEID ORDER BY UpdateCounter ASC) as rnEarly,
      ROW_NUMBER() OVER (PARTITION BY RISEID ORDER BY UpdateCounter DESC) as rnLate
    from tbl_Partner
)
SELECT t1.RecordID,t1.RISEID,t2.UpdateCounter
FROM
   OrderedRows t1
       inner join
   OrderedRows t2
       on
            t1.RISEID = t2.RISEID
WHERE
    t1.rnEarly = 1 and
    t2.rnLate = 1

这可能需要更少的桌子扫描。

Answer 2

选择最大更新计数器

 select distinct(
 (select top 1 recordid from tbl_partner p2 where p2.riseid=p1.riseid p2.updatecounter ASC
 ) )as RecordID,(select  Max(updatecounter) from tbl_partner p2 where p2.riseid=p1.riseid   ) as MaxUpdateCounter , riseid  from tbl_partner p1