create procedure sp_sum as insert into Ttotal select a.company_id,a.company_name,sum(b.total),b.play_month from Tcompany as a,Taaa as b where a.company_id = b.company_id and month(b.play_month)=month(getdate())-1 create procedure sp_sum as insert into Ttotal select a.company_id,a.company_name,sum(b.total),b.play_month from Tcompany as a,Tbbb as b where a.company_id = b.company_id and month(b.play_month)=month(getdate())-1 每月一号在job中调用
视图: v: select * from taaa union select * from tbbb union select * from tccc汇总: Insert into TTotal (company_id, total, play_month) select companyid, Sum(total), convert(varchar(7), play_date, 111) from v group by companyid, convert(varchar(7), play_date, 111) where play_date >= '06/01/2003' and play_date < '07/01/2003'
建立一个JOB, 定期每月一日执行, 执行下列SQL脚本: insert Ttotal ( select A.company_id, B.company_name, sum(A.total) [total], convert(char(7), getdate(), 120) from Taaa A left join Tcompany B on A.company_id = B.company_id where convert(char(7), play_date, 120) = convert(char(7), getdate(), 120) group by A.company_id, B.company_name unoin all select A.company_id, B.company_name, sum(A.total) [total], convert(char(7), getdate(), 120) from Tbbb A left join Tcompany B on A.company_id = B.company_id where convert(char(7), play_date, 120) = convert(char(7), getdate(), 120) group by A.company_id, B.company_name) 注意启动代理服务。
select * from Tcompany a left join (select company_id sum(total)Total,convert(char(7),play_date,120) play_Date from (select * from taaa union all select * from Tbbb) temp group by company_id ,convert(char(7),play_date,120))b on a.company_id =b.company_id
这种问题只有用游标才能做到扩展性: select convert(char(7),getdate(),111) ---06:05:05 declare cursor_insert cursor for select company_id,company_name,tablename from Tcompany declare @a varchar(100),@b varchar(100),@c varchar(100),@sql varchar(8000) open cursor_insert fetch cursor_insert into @a,@b,@c while @@fetch_status=0 begin set @sql='insert Ttotal (company_id,company_name,total,play_month) select company_id,'''+@b+''',sum(total),convert(char(7),getdate(),111) from '+@c+' where datediff(month,play_date,getdate())=1' exec(@sql) fetch cursor_insert into @a,@b,@c end close cursor_insert deallocate cursor_insert
as
insert into Ttotal
select a.company_id,a.company_name,sum(b.total),b.play_month
from Tcompany as a,Taaa as b
where a.company_id = b.company_id and month(b.play_month)=month(getdate())-1
create procedure sp_sum
as
insert into Ttotal
select a.company_id,a.company_name,sum(b.total),b.play_month
from Tcompany as a,Tbbb as b
where a.company_id = b.company_id and month(b.play_month)=month(getdate())-1
每月一号在job中调用
v: select * from taaa union
select * from tbbb union
select * from tccc汇总:
Insert into TTotal (company_id, total, play_month)
select companyid, Sum(total), convert(varchar(7), play_date, 111)
from v group by companyid, convert(varchar(7), play_date, 111)
where play_date >= '06/01/2003' and play_date < '07/01/2003'
定期每月一日执行,
执行下列SQL脚本:
insert Ttotal (
select A.company_id, B.company_name, sum(A.total) [total], convert(char(7), getdate(), 120)
from Taaa A left join Tcompany B on A.company_id = B.company_id
where convert(char(7), play_date, 120) = convert(char(7), getdate(), 120)
group by A.company_id, B.company_name
unoin all
select A.company_id, B.company_name, sum(A.total) [total], convert(char(7), getdate(), 120)
from Tbbb A left join Tcompany B on A.company_id = B.company_id
where convert(char(7), play_date, 120) = convert(char(7), getdate(), 120)
group by A.company_id, B.company_name)
注意启动代理服务。
(select company_id sum(total)Total,convert(char(7),play_date,120) play_Date
from
(select * from taaa
union all
select * from Tbbb) temp
group by company_id ,convert(char(7),play_date,120))b
on a.company_id =b.company_id
select convert(char(7),getdate(),111) ---06:05:05
declare cursor_insert cursor for select company_id,company_name,tablename from Tcompany
declare @a varchar(100),@b varchar(100),@c varchar(100),@sql varchar(8000)
open cursor_insert
fetch cursor_insert into @a,@b,@c
while @@fetch_status=0
begin
set @sql='insert Ttotal (company_id,company_name,total,play_month) select company_id,'''+@b+''',sum(total),convert(char(7),getdate(),111) from '+@c+' where datediff(month,play_date,getdate())=1'
exec(@sql)
fetch cursor_insert into @a,@b,@c
end
close cursor_insert
deallocate cursor_insert