一个关于时间 比例 的统计问题

Question

select 台站号,文件类型号,
ltrim(cast(sum(case when 达到类型=2 then 1 else 0 end)*100.0/count(*) as decimal(18,2)))+'%'
from 表
group by 台站号,文件类型号

Answer 1

set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(台站号 int,文件类型号 int,时间 datetime,达到类型 int)     
insert tb select 2        ,1,        '2009-01-01 00:00:00.000' ,2        
insert tb select 2        ,1 ,       '2009-01-02 00:00:00.000' ,1        
insert tb select 1        ,2  ,      '2009-01-02 03:00:00.000' ,2        
insert tb select 1        ,2   ,     '2009-01-01 07:00:00.000' ,1        
insert tb select 2        ,2    ,    '2009-01-03 08:00:00.000' ,2        
insert tb select 2       , 1     ,   '2009-01-01 00:00:00.000' ,2        
insert tb select 2      ,  1      ,  '2009-01-01 15:00:00.000' ,1        
insert tb select 2     ,   1       , '2009-01-02 00:00:00.000' ,1        
insert tb select 1    ,    2        ,'2009-01-02 04:00:00.000' ,1        
insert tb select 1   ,     2        ,'2009-01-01 01:00:00.000' ,3        
insert tb select 2  ,      2        ,'2009-01-04 12:13:11.000' ,3        
insert tb select 2 ,       1        ,'2009-01-01 12:00:00.000' ,2        
insert tb select 1,        1        ,'2009-01-01 15:00:00.000', 3 
select 台站号 , 文件类型号,ltrim(sum(case when 达到类型=2 then 1 else 0 end )*100.0/count(*))+'%' from tb
group by 台站号 , 文件类型号
/*台站号         文件类型号       
----------- ----------- ------------------------------------------
1           1           0.000000000000%
2           1           50.000000000000%
1           2           25.000000000000%
2           2           50.000000000000%*/

Answer 2

set nocount on
if object_id('tb')is not null drop table tb
go
create table tb(台站号 int,文件类型号 int,时间 datetime,达到类型 int)     
insert tb select 2        ,1,        '2009-01-01 00:00:00.000' ,2        
insert tb select 2        ,1 ,       '2009-01-02 00:00:00.000' ,1        
insert tb select 1        ,2  ,      '2009-01-02 03:00:00.000' ,2        
insert tb select 1        ,2   ,     '2009-01-01 07:00:00.000' ,1        
insert tb select 2        ,2    ,    '2009-01-03 08:00:00.000' ,2        
insert tb select 2       , 1     ,   '2009-01-01 00:00:00.000' ,2        
insert tb select 2      ,  1      ,  '2009-01-01 15:00:00.000' ,1        
insert tb select 2     ,   1       , '2009-01-02 00:00:00.000' ,1        
insert tb select 1    ,    2        ,'2009-01-02 04:00:00.000' ,1        
insert tb select 1   ,     2        ,'2009-01-01 01:00:00.000' ,3        
insert tb select 2  ,      2        ,'2009-01-04 12:13:11.000' ,3        
insert tb select 2 ,       1        ,'2009-01-01 12:00:00.000' ,2        
insert tb select 1,        1        ,'2009-01-01 15:00:00.000', 3 
select 台站号 , 文件类型号,convert(varchar(10),时间,120)时间,left(ltrim(sum(case when 达到类型=2 then 1 else 0 end )*100.0/count(*)),5)+'%' from tb
group by 台站号 , 文件类型号,convert(varchar(10),时间,120)
/*台站号         文件类型号       时间         
----------- ----------- ---------- ------
1           1           2009-01-01 0.000%
1           2           2009-01-01 0.000%
1           2           2009-01-02 50.00%
2           1           2009-01-01 75.00%
2           1           2009-01-02 0.000%
2           2           2009-01-03 100.0%
2           2           2009-01-04 0.000%
*/

Answer 3

台站号         文件类型            日期（天）                   迟报率            
1          1          2009-01-02 00:00:00.000 0.00  %
1          2          2009-01-02 00:00:00.000         50.00 %
1          2          2009-01-03 00:00:00.000 50.00 %如上面的结果，不知道怎么写SQL 语句！