当SQL Server中存在重复行时，如何选择具有max值的行?

I have table like this

我有这样的桌子

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

I expect result like this

我期待这样的结果

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'

4 个解决方案

#1

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'

or rewritten as a 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.

SQLfiddle是查询的两个版本。

#2

This should be faster than a self-join because (most probably) only a single scan over the table is required.

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

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

#3

Try This

试试这个

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

OUTPUT:

输出:

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

#4

-1

The simple table (Student_Course) currently is like this with duplicate records assume:

简单的表(Student_Course)目前是这样的，重复记录假设:

Sname   Sid
------------
Ravi    S001
Ravi    S001
Jack    S002
Jack    S002
Jack    S002
Guru    S003
Smith   S004
Arun    S005
Rajeev  S006

Query:

查询:

SELECT S.Sid, S.Sname, Count(*) As Occurance
FROM Student_Course AS S
GROUP BY S.Sid
HAVING Count(S.Sid) > 1
Order By Count(*) Desc Limit 1

Output:

输出:

Sid     Sname   Occurance
-------------------------
S002    Jack    3

#1