再问个sql语句问题 T_T

Question

表结构如下 时间                   状态               号码
 08:45:00             接通              1111
 08:20:20             未通              2222    
 9:10:10              未通              1111     
 9:12:18              接通              3333    
 9:12:10              未通              2222
 08:58:12             接通              2222                  
 ... 
 .. 
 . 
 23:58:10    ..... 
请问怎么用select语句显示如下效果呢?    时段                接通        未通     总数   号码数
08:00-09:00       2          1        3     2
9:00-10:00        1          2        3     3
10:00-11:00       0          0        0     0 
.... 
... 
.. 
. 
23:00-23:59       呃。。请高手指教了！

Answer 1

if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (time datetime,state varchar(6))
insert into #T
select '08:45:00','完成' union all
select '08:20:20','未完成' union all
select '9:10:10','未完成' union all
select '9:12:18','完成' union all
select '9:12:10','未完成'
go
create table #(time varchar(20),starttime datetime,endtime datetime)declare @i int
set @i=8
while @i<=22
begin
   insert into # values(right('00'+ltrim(@i),2)+':00-'+right('00'+ltrim(@i+1),2)+':00',
       convert(datetime,right('00'+ltrim(@i),2)+':00:00'),
       convert(datetime,right('00'+ltrim(@i+1),2)+':00:00'))
   set @i=@i+1
end
insert into # values('23:00-23:59',convert(datetime,'23:00:00'),convert(datetime,'23:59:59'))select b.time,
       sum(case when a.state='完成' then 1 else 0 end) [完成],
       sum(case when a.state='未完成' then 1 else 0 end) [未完成],       sum(case when a.state='完成' then 1 else 0 end)+
       sum(case when a.state='未完成' then 1 else 0 end) [总数]
from #T a
 right join
   # b
  on convert(varchar(8),a.time,108)>=convert(varchar(8),b.starttime,108)
       and convert(varchar(8),a.time,108)<convert(varchar(8),b.endtime,108)
group by b.time
go
drop table #T,#/*
time                 完成          未完成         总数          
-------------------- ----------- ----------- ----------- 
08:00-09:00          1           1           2
09:00-10:00          1           2           3
10:00-11:00          0           0           0
11:00-12:00          0           0           0
12:00-13:00          0           0           0
13:00-14:00          0           0           0
14:00-15:00          0           0           0
15:00-16:00          0           0           0
16:00-17:00          0           0           0
17:00-18:00          0           0           0
18:00-19:00          0           0           0
19:00-20:00          0           0           0
20:00-21:00          0           0           0
21:00-22:00          0           0           0
22:00-23:00          0           0           0
23:00-23:59          0           0           0（所影响的行数为 16 行）
*/

Answer 2

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (时间 datetime,状态 varchar(4),号码 int)
insert into #T
select '08:45:00','接通',1111 union all
select '08:20:20','未通',2222 union all
select '9:10:10','未通',1111 union all
select '9:12:18','接通',3333 union all
select '9:12:10','未通',2222 union all
select '08:58:12','接通',2222select
时段=datepart(hour,时间),
接通=sum(case 状态 when '接通' then 1 else 0 end),
未通=sum(case 状态 when '未通' then 1 else 0 end),
接通=count(1),
号码数=count(distinct 号码)
from #T
group by datepart(hour,时间)/*
时段        接通        未通        接通        号码数
----------- ----------- ----------- ----------- -----------
8           2           1           3           2
9           1           2           3           3
*/

Answer 3

TO liangCK 不是昨天那个了啊，，多了个问题，，要列出时间段内打了多少个不同的号码

Answer 4

if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (time datetime,state varchar(6),号码 varchar(10))
insert into #T select  '08:45:00'   ,          '接通' ,             '1111' 
insert into #T select  '08:20:20'   ,          '未通' ,             '2222'     
insert into #T select  '9:10:10'    ,          '未通' ,             '1111'      
insert into #T select  '9:12:18'    ,          '接通' ,             '3333'     
insert into #T select  '9:12:10'    ,          '未通' ,             '2222' 
insert into #T select  '08:58:12'   ,          '接通' ,             '2222'
gocreate table #(time varchar(20),starttime datetime,endtime datetime)declare @i int
set @i=8
while @i<=22
begin
   insert into # values(right('00'+ltrim(@i),2)+':00-'+right('00'+ltrim(@i+1),2)+':00',
       convert(datetime,right('00'+ltrim(@i),2)+':00:00'),
       convert(datetime,right('00'+ltrim(@i+1),2)+':00:00'))
   set @i=@i+1
end
insert into # values('23:00-23:59',convert(datetime,'23:00:00'),convert(datetime,'23:59:59'))select b.time,
       sum(case when a.state='接通' then 1 else 0 end) [接通],
       sum(case when a.state='未通' then 1 else 0 end) [未通],       sum(case when a.state='接通' then 1 else 0 end)+
       sum(case when a.state='未通' then 1 else 0 end) [总数]
       ,count(distinct 号码) 号码数
from #T a
 right join
   # b
  on convert(varchar(8),a.time,108)>=convert(varchar(8),b.starttime,108)
       and convert(varchar(8),a.time,108)<convert(varchar(8),b.endtime,108)
group by b.time
go
drop table #T,#
/*
time                 接通          未通          总数          号码数         
-------------------- ----------- ----------- ----------- ----------- 
08:00-09:00          2           1           3           2
09:00-10:00          1           2           3           3
10:00-11:00          0           0           0           0
11:00-12:00          0           0           0           0
12:00-13:00          0           0           0           0
13:00-14:00          0           0           0           0
14:00-15:00          0           0           0           0
15:00-16:00          0           0           0           0
16:00-17:00          0           0           0           0
17:00-18:00          0           0           0           0
18:00-19:00          0           0           0           0
19:00-20:00          0           0           0           0
20:00-21:00          0           0           0           0
21:00-22:00          0           0           0           0
22:00-23:00          0           0           0           0
23:00-23:59          0           0           0           0（所影响的行数为 16 行）
*/

Answer 5

SELECT DATEPART(HOUR,时间),SUM(CASE WHEN 状态='接通' THEN 1 ELSE 0 END) AS 接通，SUM(CASE WHEN 状态='未通' THEN 1 ELSE 0 END) AS 未通，COUNT(*) 总数,COUNT(DISTINCT 号码) 号码数 FROM TABLE