select *, flag=(case when (select count(*) from tb where datediff(mi,t.a,a)<15)>15 then '>15' else '<15' end) from tb t
with t(id,dt,flag) as( select 1,'2012-11-22 09:38:00',null union all select 2,'2012-11-22 09:39:00',null union all select 3,'2012-11-22 09:40:00',null union all select 4,'2012-11-22 09:41:00',null union all select 5,'2012-11-22 09:42:00',null union all select 6,'2012-11-22 09:43:00',null union all select 7,'2012-11-22 09:44:00',null union all select 8,'2012-11-22 09:45:00',null union all select 9,'2012-11-22 09:46:00',null union all select 10,'2012-11-22 09:47:00',null union all select 11,'2012-11-22 09:48:00',null union all select 12,'2012-11-22 09:49:00',null union all select 13,'2012-11-22 09:50:00',null union all select 14,'2012-11-22 09:51:00',null union all select 15,'2012-11-22 09:52:00',null union all select 16,'2012-11-22 09:53:00',null union all select 17,'2012-11-22 09:54:00',null union all select 18,'2012-11-22 09:55:00',null union all select 19,'2012-11-22 09:57:00',null union all select 20,'2012-11-22 09:58:00',null ) --如果你的表有这个id字段 select id, dt, flag=(select count(1) from t where t.dt >=dateadd(mi,-15,a.dt) and t.dt<a.dt) from t a --如果没有 /* id dt flag ----------- ------------------- ----------- 1 2012-11-22 09:38:00 0 2 2012-11-22 09:39:00 1 3 2012-11-22 09:40:00 2 4 2012-11-22 09:41:00 3 5 2012-11-22 09:42:00 4 6 2012-11-22 09:43:00 5 7 2012-11-22 09:44:00 6 8 2012-11-22 09:45:00 7 9 2012-11-22 09:46:00 8 10 2012-11-22 09:47:00 9 11 2012-11-22 09:48:00 10 12 2012-11-22 09:49:00 11 13 2012-11-22 09:50:00 12 14 2012-11-22 09:51:00 13 15 2012-11-22 09:52:00 14 16 2012-11-22 09:53:00 15 17 2012-11-22 09:54:00 15 18 2012-11-22 09:55:00 15 19 2012-11-22 09:57:00 14 20 2012-11-22 09:58:00 14(20 行受影响) */ --这样一目了然 该怎么处理自己就会了吧
flag=(case when (select count(*) from tb where datediff(mi,t.a,a)<15)>15 then '>15' else '<15' end)
from tb t
as(
select 1,'2012-11-22 09:38:00',null union all
select 2,'2012-11-22 09:39:00',null union all
select 3,'2012-11-22 09:40:00',null union all
select 4,'2012-11-22 09:41:00',null union all
select 5,'2012-11-22 09:42:00',null union all
select 6,'2012-11-22 09:43:00',null union all
select 7,'2012-11-22 09:44:00',null union all
select 8,'2012-11-22 09:45:00',null union all
select 9,'2012-11-22 09:46:00',null union all
select 10,'2012-11-22 09:47:00',null union all
select 11,'2012-11-22 09:48:00',null union all
select 12,'2012-11-22 09:49:00',null union all
select 13,'2012-11-22 09:50:00',null union all
select 14,'2012-11-22 09:51:00',null union all
select 15,'2012-11-22 09:52:00',null union all
select 16,'2012-11-22 09:53:00',null union all
select 17,'2012-11-22 09:54:00',null union all
select 18,'2012-11-22 09:55:00',null union all
select 19,'2012-11-22 09:57:00',null union all
select 20,'2012-11-22 09:58:00',null
)
--如果你的表有这个id字段
select
id,
dt,
flag=(select count(1) from t where t.dt >=dateadd(mi,-15,a.dt) and t.dt<a.dt)
from
t a
--如果没有
/*
id dt flag
----------- ------------------- -----------
1 2012-11-22 09:38:00 0
2 2012-11-22 09:39:00 1
3 2012-11-22 09:40:00 2
4 2012-11-22 09:41:00 3
5 2012-11-22 09:42:00 4
6 2012-11-22 09:43:00 5
7 2012-11-22 09:44:00 6
8 2012-11-22 09:45:00 7
9 2012-11-22 09:46:00 8
10 2012-11-22 09:47:00 9
11 2012-11-22 09:48:00 10
12 2012-11-22 09:49:00 11
13 2012-11-22 09:50:00 12
14 2012-11-22 09:51:00 13
15 2012-11-22 09:52:00 14
16 2012-11-22 09:53:00 15
17 2012-11-22 09:54:00 15
18 2012-11-22 09:55:00 15
19 2012-11-22 09:57:00 14
20 2012-11-22 09:58:00 14(20 行受影响)
*/
--这样一目了然 该怎么处理自己就会了吧