group by having解决方法

create table test(c1 varchar(10),c2 datetime,c3 datetime,c4 int)
go
insert into test
select 'A',dateadd(mm,-20,getdate()),getdate(),1 union all
select 'A',dateadd(mm,-15,getdate()),getdate(),2 union all
select 'B',dateadd(mm,0,getdate()),getdate(),3 union all
select 'C',dateadd(mm,0,getdate()),getdate(),3 union all
select 'C',dateadd(mm,0,getdate()),getdate(),4 union all
select 'D',dateadd(mm,0,getdate()),getdate(),4 union all
select 'E',null,getdate(),5 

/**根据c1分组,c3排序,c4不等于5
   排除超过3条的记录和3条以下且c2距离当前时间为20分钟以内(2条记录取max(c2))(c2为null的不在这范围内)
**/

按你的要求写的
悲剧，你要的四AE，我查询出来的BCD

select * from
(
select 
c1,c2,c3,c4,
COUNT(*)over(partition by c1) as ct,
ROW_NUMBER()over(partition by c1 order by c3 desc) as rm
from test
where 
c4!='5'
and 
c2 >dateadd(MI,-20,GETDATE())
) t where rm=1 and ct<3


c1         c2                      c3                      c4          ct          rm
---------- ----------------------- ----------------------- ----------- ----------- --------------------
B          2014-09-01 11:11:27.237 2014-09-01 11:11:27.237 3           1           1
C          2014-09-01 11:11:27.237 2014-09-01 11:11:27.237 3           2           1
D          2014-09-01 11:11:27.237 2014-09-01 11:11:27.237 4           1           1

(3 行受影响)

WITH test (id,c1,c2,c3,c4) AS(
select 1,'A',dateadd(mm,-40,getdate()),getdate(),1 union all
select 2,'A',dateadd(mm,-21,getdate()),getdate(),2 union all
select 3,'B',dateadd(mm,0,getdate()),getdate(),3 union all
select 4,'C',dateadd(mm,-60,getdate()),getdate(),3 union all
select 5,'C',dateadd(mm,-40,getdate()),getdate(),4 union all
select 6,'C',dateadd(mm,-10,getdate()),getdate(),3 union all
select 7,'D',dateadd(mm,0,getdate()),getdate(),4 union all
select 8,'E',null,getdate(),4  union all
select 9,'F',null,getdate(),5 
),
o AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY c2 DESC) n
      FROM test
)
,
g AS (
    SELECT c1,
           count(*) c
      FROM test
  GROUP BY c1
    HAVING count(*) < 3
)
SELECT o.id,o.c1,o.c2,o.c3,o.c4
  FROM o
  JOIN g
    ON g.c1 = o.c1
 WHERE o.n=1
   AND o.c4<>5
   AND (   datediff(minute,o.c2,getdate())>20.0
        OR o.c2 IS NULL
       )

         id c1   c2                      c3                               c4
----------- ---- ----------------------- ----------------------- -----------
          2 A    2012-12-01 11:26:21.227 2014-09-01 11:26:21.227           2
          8 E    NULL                    2014-09-01 11:26:21.227           4