SQL Server中存在重复行时如何选择具有最大值的行 [英] How to select row with max value when duplicate rows exist in SQL Server

问题描述

我有这样的桌子

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

我尝试了此查询，但返回的结果与我预期的不一样

I try this query but it's return not like what I expect

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'

推荐答案

您基本上只是缺少状态比较，因为您希望每个状态占一行；

You're basically just missing a status comparison since you want one row per status;

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 在此处提供两个版本的查询.

这篇关于SQL Server中存在重复行时如何选择具有最大值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！