22,207
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb;
GO
CREATE TABLE tb(id int, mid int, starttime datetime);
INSERT INTO tb(id, starttime)
select 1, '2012-04-01 08:00:00' union all
select 1, '2012-04-01 15:20:00' union all
select 1, '2012-04-02 00:50:00' union all
select 1, '2012-04-02 8:50:00' union all
select 1, '2012-04-02 18:50:00' union all
select 1, '2012-04-03 5:50:00' union all
select 2, '2012-04-04 5:50:00' union all
select 2, '2012-04-04 8:50:00' union all
select 2, '2012-04-04 15:50:00';
--select id, starttime from tb;
select a.id, sum([count]) as 次数
from ( select id, max(num) as [Count]
from (
select a.id, a.starttime, 1 as num
from tb a
where a.starttime >= '2012-04-01' and a.starttime <= '2012-04-12'
and convert(varchar(8), a.starttime, 108) between '05:30:00' and '14:30:00'
and (exists ( select 1 from tb b
where a.id = b.id
and b.starttime between cast( convert(varchar(10), a.starttime, 120) + ' 05:30:00' as datetime)
and cast( convert(varchar(10), a.starttime, 120) + ' 14:30:00' as datetime)
)
)
and (exists ( select 1 from tb b1
where a.id = b1.id
and b1.starttime > cast( convert(varchar(10), a.starttime, 120) + ' 14:30:00' as datetime)
and b1.starttime <= cast( convert(varchar(10), a.starttime + 1, 120) + ' 14:30:00' as datetime)
)
)
) t
group by id, convert(varchar(10), starttime, 120)
) a
group by a.id
/*
id 次数
----------- -----------
1 2
2 1
*/
/*
id starttime
1 2012-04-01 08:00:00
1 2012-04-01 15:20:00
1 2012-04-02 00:50:00
当天的5:30:00到14:30:00有记录,
并且在当天的14:30:01到第二天的14:30:00也有记录,那么就记一次
*/
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb;
GO
CREATE TABLE tb(id int, mid int, starttime datetime);
INSERT INTO tb(id, starttime)
select 1, '2012-04-01 08:00:00' union all
select 1, '2012-04-01 15:20:00' union all
select 1, '2012-04-02 00:50:00' union all
select 1, '2012-04-02 8:50:00' union all
select 1, '2012-04-02 18:50:00' union all
select 1, '2012-04-03 5:50:00' union all
select 2, '2012-04-04 5:50:00' union all
select 2, '2012-04-04 8:50:00' union all
select 2, '2012-04-04 15:50:00';
--select id, starttime from tb;
with T as
(
select a.id, a.starttime, 1 as num
from tb a
where a.starttime >= '2012-04-01' and a.starttime <= '2012-04-12'
and convert(varchar(8), a.starttime, 108) between '05:30:00' and '14:30:00'
and (exists ( select 1 from tb b
where a.id = b.id
and b.starttime between cast( convert(varchar(10), a.starttime, 120) + ' 05:30:00' as datetime)
and cast( convert(varchar(10), a.starttime, 120) + ' 14:30:00' as datetime)
)
)
and (exists ( select 1 from tb b1
where a.id = b1.id
and b1.starttime > cast( convert(varchar(10), a.starttime, 120) + ' 14:30:00' as datetime)
and b1.starttime <= cast( convert(varchar(10), a.starttime + 1, 120) + ' 14:30:00' as datetime)
)
)
)
select a.id, sum([count]) as 次数
from ( select id, max(num) as [Count] from T
group by id, convert(varchar(10), starttime, 120)
) a
group by a.id