SQL Server中存在重复行时如何选择具有最大值的行

Question

我有一张这样的桌子

DocumentID        | MasterStepID | StepNumber | RoleID | UserID     | Status
JIEP/TT/07/000174 | Approval1    |          1 |   NULL | 0006100022 |      1    
JIEP/TT/07/000174 | Approval1    |          2 |     12 | 0006199013 |      3    
JIEP/TT/07/000174 | Approval1    |          3 |     13 | 0006106426 |      3
JIEP/TT/07/000174 | Approval1    |          5 |     18 | 0006100022 |      3
JIEP/TT/07/000174 | Approval1    |          6 |     16 | 0006104115 |      6

我希望得到这样的结果

JIEP/TT/07/000174 | Approval1 | 1 | NULL | 0006100022 | 1
JIEP/TT/07/000174 | Approval1 | 5 |   18 | 0006100022 | 3
JIEP/TT/07/000174 | Approval1 | 6 |   16 | 0006104115 | 6

我尝试了这个查询，但返回的结果并不像我期望的那样

select  *
from    WF_Approval sr1
where not exists
        (
        select  *
        from    WF_Approval sr2 
        where   sr1.DocumentID = sr2.DocumentID and 
                (
                    sr1.StepNumber < sr2.StepNumber
                )
        )and MasterStepID = 'Approval1'

Answer 1

你基本上只是错过了一个状态比较，因为你想要每个状态一行；

SELECT *
FROM WF_Approval sr1
WHERE NOT EXISTS (
    SELECT *
    FROM  WF_Approval sr2 
    WHERE sr1.DocumentID = sr2.DocumentID AND 
          sr1.Status = sr2.Status AND                  # <-- new line
          sr1.StepNumber < sr2.StepNumber
) AND MasterStepID = 'Approval1'

或者重写为JOIN；

SELECT *
FROM WF_Approval sr1
LEFT JOIN WF_Approval sr2
  ON sr1.DocumentID = sr2.DocumentID 
 AND sr1.Status = sr2.Status
 AND sr1.StepNumber < sr2.StepNumber
WHERE sr2.DocumentID IS NULL
  AND sr1.MasterStepID = 'Approval1';

SQLfiddle with both versions of the query here。

Answer 2

这应该比自连接更快，因为(很可能)只需要对表进行一次扫描。

select DocumentID, 
       MasterStepID, 
       StepNumber, 
       RoleID,
       UserID , 
       Status
from (
    select wf.*
           row_number() over (partition by wf.status order by wf.stepnumber desc) as rn
    from  WF_Approval wf
) t
where rn = 1
order by StepNumber

Answer 3

尝尝这个

SELECT T1.* FROM WF_Approval T1 
JOIN
(SELECT DISTINCT [Status], DocumentID, MAX(StepNumber) as [StepNumber] from WF_Approval 
GROUP BY DocumentID, [Status])  T2 
ON T1.DocumentID = T2.DocumentID AND T1.[Status] = T2.[Status]
AND T1.StepNumber = T2.StepNumber
ORDER BY StepNumber ASC, Status ASC

输出：

JIEP/TT/07/000174   Approval1   1   NULL    0006100022  1
JIEP/TT/07/000174   Approval1   5   18      0006100022  3
JIEP/TT/07/000174   Approval1   6   16      0006104115  6