select [category]=(select top 1 category from resource where registerno=t.registerno),
[avg]=count(1)/(select count(1) from usageinfo)
from usageinfo t
group by (select top 1 category from resource where registerno=t.registerno)
[avg]=count(1)/(select count(1) from usageinfo)
from usageinfo t
group by (select top 1 category from resource where registerno=t.registerno)
(
registerno varchar(10),
rname varchar(10),
category varchar(10)
)
create table usageinfo
(
registerno varchar(10),
period datetime
)
insert resource
select 'A001','石油1号','燃料1' union
select 'A002','石油2号','燃料2' union
select 'A003','石油3号','燃料3' union
select 'A004','石油4号','燃料4' union
select 'A005','石油5号','燃料5'
insert usageinfo
select 'A001','2005-02-02' union
select 'A001','2005-02-02' union
select 'A001','2005-02-02' union
select 'A001','2005-02-02' union
select 'A002','2005-02-02' --测试
select [category],
[avg]=convert(decimal(12,2),(count(1)+0.0)/(select count(1) from usageinfo))
from
(
select registerno ,
[category]=(select top 1 category from resource where registerno=aa.registerno)
from usageinfo aa
)t
group by [category] --删除测试环境
drop table resource,usageinfo--结果
/*
category avg
---------- --------------
燃料1 .50
燃料2 .50(2 row(s) affected)
*/
create table resource
(
registerno varchar(10),
rname varchar(10),
category varchar(10)
)
create table usageinfo
(
registerno varchar(10),
period datetime
)
insert resource
select 'A001','石油1号','燃料1' union
select 'A002','石油2号','燃料2' union
select 'A003','石油3号','燃料3' union
select 'A004','石油4号','燃料4' union
select 'A005','石油5号','燃料5'
insert usageinfo
select 'A001','2005-02-02' union
select 'A001','2005-02-02' union
select 'A001','2005-02-02' union
select 'A001','2005-02-02' union
select 'A002','2005-02-02' --测试select bb.category,
[num]=sum(case when aa.period is not null then 1 else 0 end)
into #
from usageinfo aa
right join resource bb on aa.registerno=bb.registerno
group by bb.category
select category,
[avg]=convert(decimal(12,2),(num+0.0)/(select count(1) from # where num>0))
from #
--删除测试环境
drop table #
drop table resource,usageinfo--结果
/*
category avg
---------- --------------
燃料1 .50
燃料2 .50
燃料3 .00
燃料4 .00
燃料5 .00(5 row(s) affected)
*/