条件是你已知id, y_month, m_month的值分别为l_id,l_ymonth,l_mmonth
insert into t_b(id,y_month,m_month,count_all,bad_no1,bad_no2,bad_no3,bad_no4)
(select l_id,l_ymonth,l_mmonth,max(b.all_bad),sum(decode(a.rownum,1,bad_no,0)),
sum(decode(a.rownum,2,bad_no,0)),sum(decode(a.rownum,3,bad_no,0)),
sum(decode(a.rownum,4,bad_no,0)) from
( select rownum no,bad_no from t_a where id=l_id and y_month=l_ymonth and m_month=l_mmonth) a,
( select count(*) all_bad from t_a where id=l_id and y_month=l_ymonth and m_month=l_mmonth) b
insert into t_b(id,y_month,m_month,count_all,bad_no1,bad_no2,bad_no3,bad_no4)
(select l_id,l_ymonth,l_mmonth,max(b.all_bad),sum(decode(a.rownum,1,bad_no,0)),
sum(decode(a.rownum,2,bad_no,0)),sum(decode(a.rownum,3,bad_no,0)),
sum(decode(a.rownum,4,bad_no,0)) from
( select rownum no,bad_no from t_a where id=l_id and y_month=l_ymonth and m_month=l_mmonth) a,
( select count(*) all_bad from t_a where id=l_id and y_month=l_ymonth and m_month=l_mmonth) b
将所有的a.rownum改为a.no
这里假设bad_no里的值只能是11,22,33,44
那么
insert into t_b
select id,y_month,m_month,count(1),
11,sum(decode(bad_no,11,1,0)),
22,sum(decode(bad_no,22,1,0)),
33,sum(decode(bad_no,33,1,0)),
44,sum(decode(bad_no,44,1,0))
from t_a group by id,y_month,m_month;如果说bad_no里的值不是固定的四个值,而可能更多。
那最好用存储过程来解决。
还有我很奇怪的是干什么把查询结果存成一个表,
如果t_a中新增了记录,是不是要把t_b重新生成一遍?
bad_no 为错误代号
count_all 为错误计数总量
bad_no1,bad_no2,bad_no3,bad_no4为错误代号 同t_a表中的错误代号
如:
t_a:
recno=1,id=1,y_month=10,m_month=01,bad_no=13
recno=2,id=2,y_month=10,m_month=01,bad_no=13
recno=3,id=1,y_month=10,m_month=01,bad_no=14
recno=4,id=1,y_month=10,m_month=02,bad_no=13
recno=5,id=1,y_month=10,m_month=01,bad_no=13
四条记录插入t_b应该为
id=1,y_month=10,m_month=01,count_all=3,bad_no1=13,count_bad1=2,bad_no2=14,count_bad2=1..
id=1,y_month=10,m_month=02,count_all=1,bad_no1=13,count_bad1=1,...
id=2,y_month=10,m_month=01,count_all=1,bad_no1=13,count_bad1=1,...help!!!
不止四种,比如说t_a表是部件分析表,每条记录只有一个故障代号,
但是t_b表可以按照部件id,m_month,y_month取故障代号数量最多的前四个故障;
如果写存储过程该如何写?
l_id number :=1;
l_ymonth number :=10;
l_mmonth number :=1;
begin
insert into t_b(id,y_month,m_month,count_all,bad_no1,count_bad1,bad_no2,count_bad2,
bad_no3,count_bad3,bad_no4,count_bad4)
( select b.id,b.y_month,b.m_month,max(c.all_bad),sum(decode(b.no,1,bad_no,0)),sum(decode(b.no,1,count_bad,0)),
sum(decode(b.no,2,bad_no,0)),sum(decode(b.no,2,count_bad,0)),sum(decode(b.no,3,bad_no,0)),sum(decode(b.no,3,count_bad,0)),
sum(decode(b.no,4,bad_no,0)),sum(decode(b.no,4,count_bad,0)) from
( select rownum no,id,y_month,m_month,bad_no,count_bad from
( select id,y_month,m_month,bad_no,count(*) count_bad from t_a
group by id,y_month,m_month,bad_no
order by id,y_month,m_month,bad_no,count_bad desc
) a
where a.id=l_id and a.y_month=l_ymonth and a.m_month=l_mmonth and rownum < 5
) b,
( select count(distinct bad_no) all_bad from t_a where id=l_id and y_month=l_ymonth and m_month=l_mmonth) c
group by b.id,b.y_month,b.m_month
);
end;
/
谢谢空杯!
结贴!