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

Question

我有这样的 table

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 .