sql-server-2008 - 根据条件获取组中的最大值

Question

ProjID  Dno RNo Status       DateApproved
  100   1   1   Initiated   2014-12-31 09:15:58.000
  100   1   1   Approved    2015-01-31 09:15:58.000
  100   1   1   Approved    2015-02-01 09:15:58.000
  100   1   1   Approved    2015-05-28 09:15:58.000
  100   1   1   Approved    2015-06-20 09:15:58.000
  101   1   1   Approved    2014-12-31 09:15:58.000
  101   1   1   Approved    2015-01-31 09:15:58.000
  101   1   1   Approved    2015-02-01 09:15:58.000
  101   1   1   Approved    2015-05-28 09:15:58.000
  101   1   1   Approved    2015-08-20 09:15:58.000

在上面的示例中，我必须为每个 projectid 获取 max(Dateapproved) 作为 Dateapproved。如果所有修订状态都在特定组中获得批准，例如:project id=101 其组中的所有行都具有已批准状态，因此我必须获得最大日期:'2015-08-20 09:15:58.000' .但对于 Projectid=100，一个状态仍处于 Initiated 状态，因此我们必须将 Null 显示为 Dateapproved。

提前致谢

我的输出应该是这样的:

ProjId Dno Rno DateApproved
100      1  1   NUll
101      1  1   2015-08-20 09:15:58.000

示例代码:

Create table #temp(
ProjectID varchar(35),
Documentno int,
Revisionno int,
Status varchar(35),
DateApproved Datetime)

insert into #temp values ( '100',    1,  1,  'Initiated','2014-12-31 09:15:58')
insert into #temp values ( '100',    1,  1,  'Approved','2015-01-31 09:15:58 ')
insert into #temp values ( '100',    1,  1,  'Approved','2015-02-01 09:15:58 ')
insert into #temp values ( '100',    1,  1,  'Approved','2015-05-28 09:15:58 ')
insert into #temp values ( '100',    1,  1,  'Approved','2015-06-20 09:15:58 ')


insert into #temp values ( '101',    1,  1,  'Approved','2014-12-31 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-01-31 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-02-01 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-05-28 09:15:58 ')
insert into #temp values ( '101',    1,  1,  'Approved','2015-08-20 09:15:58 ')


select * from #temp  

Answer 1

试试这个:

SELECT T.ProjectID,
       Documentno as Dno, 
       Revisionno  as RNo,
       CASE WHEN SUM(CASE WHEN T.Status <> 'Approved' THEN 1 ELSE 0 END) = 0 
            THEN Max(T.DateApproved) ELSE NULL
       END as DateApproved 
from #temp T
GROUP BY T.ProjectId, Documentno , Revisionno  

当针对您的测试数据运行时，这会产生以下输出:

PROJECT ID  DNo    TNo   DateApproved
100         1      1      NULL
101         1      1      2015-08-20 09:15:58.000