insert into tblSongSelect   
select       *       
,sum(case when datediff(day,fSelectDate,getdate()) < 7   then 1 else 0 end)  as       fWeekTop     
,sum(case when datediff(day,fSelectDate,getdate()) < 30 then 1 else 0 end) as       fMonthTop      
, count(1)  as       fTotalTop 
from       tblSongInfo  a inner join tblSongTop b on a.fSongCode = b.fSOngCode

------解决方案--------------------
--怀疑楼主的统计有问题，出现了笛卡儿乘积，可尝试下分开处理如下：
--PS：我统计过30W的数据，也是做联合查询，好像没有那么慢的。

--3.   从点歌信息表中,分别统计周,月,总排行.  
update       tblSongSelect       set       --更新排行记录  
fWeekTop       =       (select       count(*)       from       tblSongTop)
where       tblSongTop.fSongCode       =       tblSongSelect.fSOngCode       and
tblSongTop.fSelectDate>       getdate()       -       7      --生成周排行


update       tblSongSelect       set       --更新排行记录  
fMonthTop       =       (select       count(*)       from       tblSongTop)
where       tblSongTop.fSongCode       =       tblSongSelect.fSOngCode       and
tblSongTop.fSelectDate>       getdate()       -       30     --生成月排行

update       tblSongSelect       set       --更新排行记录  
fTotalTop       =       (select       count(*)       from       tblSongTop)
where       tblSongTop.fSongCode       =       tblSongSelect.fSOngCode      --生成总排行