有如下一张表
id d_lrrq A1 A2 A3 A4 A5 A6 A7 A8 A9 A10
1 2010-12-8 12:02:20 2.0 2.5 2.8 1.0 2.5 2.5 2.3 2.5 2.6 5.5
2 2010-12-8 12:02:20 2.0 21.5 2.8 3.0 3.5 2.5 2.3 2.5 8.6 3.5
3 2010-11-9 12:02:20 4.0 12.5 2.8 1.0 2.5 2.5 7.3 8.5 9.6 8.5
4 2010-12-9 12:02:20 34.0 2.5 3.8 1.0 7.5 78.5 2.3 2.5 2.6 3.5
5 2010-12-9 12:02:20 23.0 2.5 34.8 1.0 2.5 2.5 2.3 2.5 8.6 0.5
6 2010-12-9 12:02:20 26.0 2.5 2.8 1.0 2.5 2.5 2.3 2.5 2.6 3.5怎么样统计出A1到A10的日平均值和月平均值!!
新手,请高手帮忙!
id d_lrrq A1 A2 A3 A4 A5 A6 A7 A8 A9 A10
1 2010-12-8 12:02:20 2.0 2.5 2.8 1.0 2.5 2.5 2.3 2.5 2.6 5.5
2 2010-12-8 12:02:20 2.0 21.5 2.8 3.0 3.5 2.5 2.3 2.5 8.6 3.5
3 2010-11-9 12:02:20 4.0 12.5 2.8 1.0 2.5 2.5 7.3 8.5 9.6 8.5
4 2010-12-9 12:02:20 34.0 2.5 3.8 1.0 7.5 78.5 2.3 2.5 2.6 3.5
5 2010-12-9 12:02:20 23.0 2.5 34.8 1.0 2.5 2.5 2.3 2.5 8.6 0.5
6 2010-12-9 12:02:20 26.0 2.5 2.8 1.0 2.5 2.5 2.3 2.5 2.6 3.5怎么样统计出A1到A10的日平均值和月平均值!!
新手,请高手帮忙!
解决方案 »
- 建表时触发器的问题
- ORA-01013和ORA-21500错误
- oracle查询问题!急
- 一个sql语句写不出来,请大家帮帮忙
- 求一个SQL语句,2个查询结果的整合
- 关于Oracl的DBLINK的问题
- ORA-12570:TNS:包阅读程序失败出错,出错。我该咋办??谁能 help me?
- ?????怪事情,高手肯定要帮忙的哦!!!??????
- 启动数据库出错怎么办呀?请各位大侠指点一下。
- Oracle数据表AA每天都会有100万的数据进入,查询的时候表特别慢,想备份一下AA表的数据,另外建一个备份表BB
- ORA-04091: 表 发生了变化, 触发器/函数不能读它
- 把用户user1所有己创建表的字段(数字类型)default value全设为零
select d_lrrq ,avg(A1),...
from tablename
group by d_lrrq--月平均
select to_char(d_lrrq,'yyyy-mm'),avg(A1)...
from tablename
group by to_char(d_lrrq,'yyyy-mm')
id d_lrrq 日平均 月平均 这四个字段的值,日平均和月平均是A1-A10十个数的和的日平均,月平均。。
我看这不简单,建议用存储过程处理吧
with tb2 as
(select trunc(dt) dt,sum(a1)+sum(a2)+sum(a3)+sum(a4)+sum(a5)+sum(6)+sum(a7)+sum(a8)+sum(a9)+sum(a10) sm,
sum(decode(sign(a1),1,1,0)+decode(sign(a2),1,1,0)+decode(sign(3),1,1,0)+decode(sign(a4),1,1,0)+decode(sign(a5),1,1,0)+decode(sign(a6),1,1,0)+decode(sign(a7),1,1,0)+
decode(sign(a8),1,1,0)+decode(sign(a9),1,1,0)+decode(sign(a10),1,1,0)) cnt
from tb group by trunc(dt))
select dt,round(sm/cnt,2) avg_total from tb2---月
with tb2 as
(select trunc(dt,'mm') dt,sum(a1)+sum(a2)+sum(a3)+sum(a4)+sum(a5)+sum(6)+sum(a7)+sum(a8)+sum(a9)+sum(a10) sm,
sum(decode(sign(a1),1,1,0)+decode(sign(a2),1,1,0)+decode(sign(3),1,1,0)+decode(sign(a4),1,1,0)+decode(sign(a5),1,1,0)+decode(sign(a6),1,1,0)+decode(sign(a7),1,1,0)+
decode(sign(a8),1,1,0)+decode(sign(a9),1,1,0)+decode(sign(a10),1,1,0)) cnt
from tb group by trunc(dt))
select dt,round(sm/cnt,2) avg_total from tb2
以前group by id d_lrrq,
现在你改成group by id ,trunc(d_lrrq,'mm')求月平均group by id ,trunc(d_lrrq,'y')求年平均