create table A (Ext int,EplName varchar(10),DialDuration datetime)
insert into A values(652, '用户1','00:01:34')
insert into A values(865, '用户2','00:02:09')
insert into A values(865, '用户2','00:00:12')
go
select * from
(
select Ext,EplName,b.m , sum(case when datepart(mi,dialduration) * 60 + datepart(ss,dialduration) >=m and datepart(mi,dialduration) * 60 + datepart(ss,dialduration)<n then 1 else 0 end) [count] from a,
(select 0 m,9 n
union all select 10 , 19
union all select 20 , 29
union all select 30 , 39
union all select 40 , 49
union all select 50 , 59
union all select 60 , 69
union all select 70 , 79
union all select 80 , 89
union all select 90 , 100
) b
group by Ext,EplName,b.m
) t where [count] > 0
order by ext,eplname,m
drop table A
/*
Ext EplName m count
----------- ---------- ----------- -----------
652 用户1 90 1
865 用户2 10 1（所影响的行数为 2 行）
*/
insert into A values(652, '用户1','00:01:34')
insert into A values(865, '用户2','00:02:09')
insert into A values(865, '用户2','00:00:12')
go
select * from
(
select Ext,EplName,b.m , sum(case when datepart(mi,dialduration) * 60 + datepart(ss,dialduration) >=m and datepart(mi,dialduration) * 60 + datepart(ss,dialduration)<n then 1 else 0 end) [count] from a,
(select 0 m,9 n
union all select 10 , 19
union all select 20 , 29
union all select 30 , 39
union all select 40 , 49
union all select 50 , 59
union all select 60 , 69
union all select 70 , 79
union all select 80 , 89
union all select 90 , 100
) b
group by Ext,EplName,b.m
) t where [count] > 0
order by ext,eplname,m
drop table A
/*
Ext EplName m count
----------- ---------- ----------- -----------
652 用户1 90 1
865 用户2 10 1（所影响的行数为 2 行）
*/
insert into A values(652, '用户1','00:01:34')
insert into A values(865, '用户2','00:00:12')
insert into A values(865, '用户2','00:02:09')
insert into A values(877, '用户3','00:00:11')
insert into A values(865, '用户2','00:02:27')
insert into A values(877, '用户3','00:00:19')
insert into A values(887, '用户4','00:01:02')
insert into A values(652, '用户1','00:01:28')
insert into A values(871, '用户5','00:00:15')
insert into A values(877, '用户3','00:00:39')
insert into A values(877, '用户3','00:00:13')
insert into A values(877, '用户3','00:00:14')
insert into A values(871, '用户5','00:00:32')
insert into A values(877, '用户3','00:00:34')
insert into A values(877, '用户3','00:00:19')
insert into A values(871, '用户5','00:00:57')
insert into A values(877, '用户3','00:00:35')
go
select * from
(
select Ext,EplName,b.m ,
sum(case when datepart(mi,dialduration) * 60 + datepart(ss,dialduration) >=m and datepart(mi,dialduration) * 60 + datepart(ss,dialduration)<n then 1 else 0 end) [count]
from a,
(select 0 m,10 n
union all select 10 , 20
union all select 20 , 30
union all select 30 , 40
union all select 40 , 50
union all select 50 , 60
union all select 60 , 70
union all select 70 , 80
union all select 80 , 90
union all select 90 , 100
union all select 100 , 120
union all select 110 , 120
union all select 120 , 130
union all select 130 , 140
union all select 140 , 150
) b
group by Ext,EplName,b.m
) t where [count] > 0
order by ext,eplname,m
drop table A
/*
Ext EplName m count
----------- ---------- ----------- -----------
652 用户1 80 1
652 用户1 90 1
865 用户2 10 1
865 用户2 120 1
865 用户2 140 1
871 用户5 10 1
871 用户5 30 1
871 用户5 50 1
877 用户3 10 5
877 用户3 30 3
887 用户4 60 1
（所影响的行数为 11 行）
*/
create table A (Ext int,EplName varchar(10),DialDuration datetime)
insert into A values(652, '用户1','00:01:34')
insert into A values(865, '用户2','00:00:12')
insert into A values(865, '用户2','00:02:09')
insert into A values(877, '用户3','00:00:11')
insert into A values(865, '用户2','00:02:27')
insert into A values(877, '用户3','00:00:19')
insert into A values(887, '用户4','00:01:02')
insert into A values(652, '用户1','00:01:28')
insert into A values(871, '用户5','00:00:15')
insert into A values(877, '用户3','00:00:39')
insert into A values(877, '用户3','00:00:13')
insert into A values(877, '用户3','00:00:14')
insert into A values(871, '用户5','00:00:32')
insert into A values(877, '用户3','00:00:34')
insert into A values(877, '用户3','00:00:19')
insert into A values(871, '用户5','00:00:57')
insert into A values(877, '用户3','00:00:35')
go
select * from
(
select Ext,EplName,b.m ,
sum(case when datepart(mi,dialduration) * 60 + datepart(ss,dialduration) >=m and datepart(mi,dialduration) * 60 + datepart(ss,dialduration)<(m+10) then 1 else 0 end) [count]
from a,
(select 0 m
union all select 10
union all select 20
union all select 30
union all select 40
union all select 50
union all select 60
union all select 70
union all select 80
union all select 90
union all select 100
union all select 110
union all select 120
union all select 130
union all select 140
union all select 150
) b
group by Ext,EplName,b.m
) t where [count] > 0
order by ext,eplname,m
drop table A
/*
Ext EplName m count
----------- ---------- ----------- -----------
652 用户1 80 1
652 用户1 90 1
865 用户2 10 1
865 用户2 120 1
865 用户2 140 1
871 用户5 10 1
871 用户5 30 1
871 用户5 50 1
877 用户3 10 5
877 用户3 30 3
887 用户4 60 1
（所影响的行数为 11 行）
*/