求一简单的查询插入存储过程

[kenesyu]

有1张统计表 A （）
time 日期
total 总数
vis0 状态０
vis1　状态１
vis2　状态２
type　表名

7张信息表 B,C,D,E,F,G,H
结构如下

vis 状态
date 日期

想实现如下功能在每天晚上0点统计 表B,C,D,E,F,G,H 里当天发布了多少条信息 每种状的信息是多少条然后插入到A表做查询用
请各位大哥帮忙！！！
例如

time total vis0 vis1　 vis2　type　
2008-4-8 100 20 30 50 表A
2008-4-8 100 20 30 50 表B

[青锋-SS]

需要使用作业.

[子陌红尘]

企业管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句:
EXEC 存储过程名 ...

--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选择"反复出现"
--点"更改"来设置你的时间安排


然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行

设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.

[youngerch]

up

[fcuandy]

[Quote=引用 2 楼 libin_ftsafe 的回复:]
企业管理器
--管理
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库
--"命令"中输入要执行的语句:
EXEC 存储过程名 ...

--确定
--"调度"项
--新建调度
--"名称"中输入调度名称
--"调度类型"中选择你的作业执行安排
--如果选…
[/Quote]

[zanyzyg]

先分组统计出结果来就好说了啊

[子陌红尘]

存储过程内容：

insert into A(time,total,vis0,vis1,vis2,type)
      select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表B' from 表B where datediff(dd,date,getdate())=1
union select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表C' from 表C where datediff(dd,date,getdate())=1
union select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表D' from 表D where datediff(dd,date,getdate())=1
union select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表E' from 表E where datediff(dd,date,getdate())=1
union select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表F' from 表F where datediff(dd,date,getdate())=1
union select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表G' from 表G where datediff(dd,date,getdate())=1
union select convert(char(10),date,120),count(*),sum(case vis when 0 then 1 else 0 end),sum(case vis when 1 then 1 else 0 end),sum(case vis when 2 then 1 else 0 end),'表H' from 表H where datediff(dd,date,getdate())=1

[kenesyu]

我就是存储过程不过写谁能帮帮我呀！我下决心要好好学了
汗！写程序好几年了就不会写这个东西

[kenesyu]

谢谢子陌老大！我去试一下^_^

[iovesuperpg]

'顶

[昵称被占用了]

insert a(time,total,vis0,vis1,vis2,type)
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'B' as type
from B
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'C' as type
from C
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'D' as type
from D
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'E' as type
from E
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'F' as type
from F
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'G' as type
from G
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),[date],120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'H' as type
from H
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))

[昵称被占用了]

晕，少了group by,查当天不要group by

insert a(time,total,vis0,vis1,vis2,type)
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'B' as type
from B
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'C' as type
from C
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'D' as type
from D
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'E' as type
from E
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'F' as type
from F
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'G' as type
from G
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))
union all 
select convert(varchar(10),getdate(),120) as Time,
   count(1) as total,
   sum(case vis when 0 then 1 else 0 end) as vis0,
   sum(case vis when 0 then 1 else 0 end) as vis1,
   sum(case vis when 0 then 1 else 0 end) as vis2,
   'H' as type
from H
where [Date]>=convert(varchar(10),getdate(),120)
and [Date]<dateadd(day,1,convert(varchar(10),getdate(),120))

[utpcb]

cretae proc 存储过程名字
{@a1 int ='',
..
参数可要可不要
}
AS
内容

[kenesyu]

结贴谢谢各位
再次严重感谢子陌大哥！！！
汗我真是惭愧！！！！居然能看懂不会写