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

小编典典

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

sql

我有这样的桌子

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'

阅读 362

2021-03-17

共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在此处使用两个版本的查询。

2021-03-17