group by having

Question

create table test(id int,c1 varchar(10),c2 datetime,c3 datetime,c4 int)
go
insert into test
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 
2 A 2012-12-01 10:32:11.943 2014-09-01 10:32:11.943 2
3 B 2014-09-01 10:32:11.943 2014-09-01 10:32:11.943 3
7 D 2014-09-01 10:32:11.943 2014-09-01 10:32:11.943 4
8 E NULL 2014-09-01 10:32:11.943 4
为什么是这几条呢。
ID 2的是比1的c1时间晚，且c2距离当前时间超过20分钟
ID 8是c4<>5,且只有1条,平且C1时间为null,标识未处理ID3和7是因为C1时间不为空，且C1时间未超过20分钟

Answer 1

希望结果是
2 A 2012-12-01 10:32:11.943 2014-09-01 10:32:11.943 2
8 E NULL 2014-09-01 10:32:11.943 4上面的3和7要排除掉，理由上面已写

Answer 2

按你的要求写的
悲剧，你要的四AE，我查询出来的BCDselect * 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 行受影响)

Answer 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

Answer 4

谢谢，安全符合我的要求。
如果加个规则呢
比方说之前的数据再加上2条
select 11,'G',dateadd(mm,-22,getdate()),getdate(),3 union all
select 12,'G',dateadd(mm,0,getdate()),getdate(),5 union all
这2个数据也要排除掉，第一条是符合条件的，但是由于第二条的c4=5,说明已经把c1为g的处理完成了，只不过是第二次才处理成功。

Answer 5

原查询完全符合要求。你看一下 o 的内容就明白了。
好的。ths