我想用存储过程将普通表中的数据查询出来然后插入到月统计表中
用作业的方式在每日空闲的时候调用存储过程
需要统计的数据有最大月值,最大月值发生的时间,最小月值,最小月值发生时间,月平均数据
谢谢了先!
普通表
tb1
ID Data TM月统计表
Tb2
ID MinData MinTm MaxData MaxTm AvgData
用作业的方式在每日空闲的时候调用存储过程
需要统计的数据有最大月值,最大月值发生的时间,最小月值,最小月值发生时间,月平均数据
谢谢了先!
普通表
tb1
ID Data TM月统计表
Tb2
ID MinData MinTm MaxData MaxTm AvgData
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
月统计表
Tb2
[code=SQL][/
ID YM MinData MinTm MaxData MaxTm AvgData
1 2010-01 10 2010-01-05 2010-01-2 50 30
1 2010-02 10 2010-01-05 2010-01-2 50 30
1 2010-02 10 2010-01-05 2010-01-2 50 30
1 2010-12 10 2010-01-05 2010-01-2 50 30
2。
3。。code]
数据库是sql2005
if exists (select 1 from sysobjects where name='test1')
drop table test1
gocreate table test1
(
id varchar(10),
Tm datetime,
data varchar(20)
)
insert into test1 select '01','2010-01-01 ','10'
insert into test1 select '01','2010-01-02 ','20'
insert into test1 select '01','2010-01-03 ','30'
insert into test1 select '01','2010-01-04 ','10'
insert into test1 select '01','2010-01-05 ','50'
insert into test1 select '01','2010-02-01 ','20'
insert into test1 select '01','2010-02-02 ','10'
insert into test1 select '01','2010-02-03 ','10'
insert into test1 select '01','2010-02-04 ','10'
insert into test1 select '01','2010-02-05 ','80' insert into test1 select '02','2010-01-01 ','110'
insert into test1 select '02','2010-01-02 ','120'
insert into test1 select '02','2010-01-03 ','130'
insert into test1 select '02','2010-01-04 ','110'
insert into test1 select '02','2010-01-05 ','150'
insert into test1 select '02','2010-02-01 ','120'
insert into test1 select '02','2010-02-02 ','110'
insert into test1 select '02','2010-02-03 ','110'
insert into test1 select '02','2010-02-04 ','110'
insert into test1 select '02','2010-02-05 ','180' if exists (select 1 from sysobjects where name='test2')
drop table test2
gocreate table test2
(
id varchar(10),
Ym varchar(10),
Maxdata varchar(20),
MaxTm varchar(20),
Mindata varchar(20),
MinTm varchar(20),
Avgdata varchar(20),
) /*
要得到这样的报表:
id Ym Maxdata MaxTm Mindata MinTm Avgdata
01 2010-01 50 2010-01-05 10 2010-01-01 24
01 2010-02 80 2010-02-05 10 2010-02-02 26
02 2010-01 150 2010-01-05 110 2010-01-01 124
02 2010-02 180 2010-02-05 110 2010-02-02 126*/
drop table test1
gocreate table test1
(
id varchar(10),
Tm datetime,
data varchar(20)
)
insert into test1 select '01','2010-01-01 ','10'
insert into test1 select '01','2010-01-02 ','20'
insert into test1 select '01','2010-01-03 ','30'
insert into test1 select '01','2010-01-04 ','10'
insert into test1 select '01','2010-01-05 ','50'
insert into test1 select '01','2010-02-01 ','20'
insert into test1 select '01','2010-02-02 ','10'
insert into test1 select '01','2010-02-03 ','10'
insert into test1 select '01','2010-02-04 ','10'
insert into test1 select '01','2010-02-05 ','80' insert into test1 select '02','2010-01-01 ','110'
insert into test1 select '02','2010-01-02 ','120'
insert into test1 select '02','2010-01-03 ','130'
insert into test1 select '02','2010-01-04 ','110'
insert into test1 select '02','2010-01-05 ','150'
insert into test1 select '02','2010-02-01 ','120'
insert into test1 select '02','2010-02-02 ','110'
insert into test1 select '02','2010-02-03 ','110'
insert into test1 select '02','2010-02-04 ','110'
insert into test1 select '02','2010-02-05 ','180' if exists (select 1 from sysobjects where name='test2')
drop table test2
gocreate table test2
(
id varchar(10),
Ym varchar(10),
Maxdata varchar(20),
MaxTm varchar(20),
Mindata varchar(20),
MinTm varchar(20),
Avgdata varchar(20),
) /*
要得到这样的报表:
id Ym Maxdata MaxTm Mindata MinTm Avgdata
01 2010-01 50 2010-01-05 10 2010-01-01 24
01 2010-02 80 2010-02-05 10 2010-02-02 26
02 2010-01 150 2010-01-05 110 2010-01-01 124
02 2010-02 180 2010-02-05 110 2010-02-02 126*/--select * from temp
--drop table temp1select id,convert(varchar(7),tm,120) ym,tm,data into temp from Test1 select id,ym,max(data)maxdata,min(data) mindata,avg(convert(int,data)) avgdate into temp1 from temp group by id,ym order by idselect a.*,b.tm maxtm into temp2 from Temp1 a,temp b
where a.id=b.id and a.maxdata=b.dataselect a.*,b.tm mindata from temp2 a,temp b
where a.id=b.id and a.mindata=b.data
01 2010-01 50 10 24 2010-01-05 00:00:00.000 2010-01-01 00:00:00.000
01 2010-02 80 10 26 2010-02-05 00:00:00.000 2010-01-01 00:00:00.000
01 2010-01 50 10 24 2010-01-05 00:00:00.000 2010-01-04 00:00:00.000
01 2010-02 80 10 26 2010-02-05 00:00:00.000 2010-01-04 00:00:00.000
01 2010-01 50 10 24 2010-01-05 00:00:00.000 2010-02-02 00:00:00.000
01 2010-02 80 10 26 2010-02-05 00:00:00.000 2010-02-02 00:00:00.000
01 2010-01 50 10 24 2010-01-05 00:00:00.000 2010-02-03 00:00:00.000
01 2010-02 80 10 26 2010-02-05 00:00:00.000 2010-02-03 00:00:00.000
01 2010-01 50 10 24 2010-01-05 00:00:00.000 2010-02-04 00:00:00.000
01 2010-02 80 10 26 2010-02-05 00:00:00.000 2010-02-04 00:00:00.000
02 2010-01 150 110 124 2010-01-05 00:00:00.000 2010-01-01 00:00:00.000
02 2010-02 180 110 126 2010-02-05 00:00:00.000 2010-01-01 00:00:00.000
02 2010-01 150 110 124 2010-01-05 00:00:00.000 2010-01-04 00:00:00.000
02 2010-02 180 110 126 2010-02-05 00:00:00.000 2010-01-04 00:00:00.000
02 2010-01 150 110 124 2010-01-05 00:00:00.000 2010-02-02 00:00:00.000
02 2010-02 180 110 126 2010-02-05 00:00:00.000 2010-02-02 00:00:00.000
02 2010-01 150 110 124 2010-01-05 00:00:00.000 2010-02-03 00:00:00.000
02 2010-02 180 110 126 2010-02-05 00:00:00.000 2010-02-03 00:00:00.000
02 2010-01 150 110 124 2010-01-05 00:00:00.000 2010-02-04 00:00:00.000
02 2010-02 180 110 126 2010-02-05 00:00:00.000 2010-02-04 00:00:00.000LZ把日期类型再用convert转化一下吧