主表:
ITEMCODE ITEMDES
1010 笔
101001 圆珠笔
101002 铅笔
101003 钢笔子表:
ITEMCODE ITEMUSED USEDTIME
101001 2 2007-02-05
101001 4 2007-02-09
101002 3 2007-02-08
101003 7 2007-02-04要生成的累计表:
ITEMCODE ITEMDES ITEMUSED
1010 笔 16
101001 圆珠笔 6
101002 铅笔 3
101003 钢笔 7累计表要实时生成,该怎么做?
ITEMCODE ITEMDES
1010 笔
101001 圆珠笔
101002 铅笔
101003 钢笔子表:
ITEMCODE ITEMUSED USEDTIME
101001 2 2007-02-05
101001 4 2007-02-09
101002 3 2007-02-08
101003 7 2007-02-04要生成的累计表:
ITEMCODE ITEMDES ITEMUSED
1010 笔 16
101001 圆珠笔 6
101002 铅笔 3
101003 钢笔 7累计表要实时生成,该怎么做?
解决方案 »
- 如果用rman直接备份到远程机器?两台都是windows2003的系统
- PL/SQL 配置中文显示乱码问题(喜欢攻克难题的朋友请进)
- oracle 连接的问题
- 触发器具体怎么用啊?
- 存储过程执行不完?
- 【大家帮我看看为什么这条SQL执行起来非常慢!!!】
- help!ora-01861 文字与格式字符串不匹配
- CREATE OR REPLACE PROCEDURE p_test(p_string in varchar2,cur_basic out cursor)是错的?
- 关于oracle多版本读一致性
- 想学oracle,没什么基础,不知道从哪学起?
- 请问能用SQL语句查询服务器上的磁盘剩余空间情况吗?
- 如何更有效的建立索引
from 主表 a left outter join 子表 b
on a.ITEMCODE=b.ITEMCODE
group by a.ITEMCODE
手边没环境,大概的意思这样,实施的话就做成view。
t1.ITEMCODE,
t1.ITEMDES,
nvl(sum(t2.ITEMUSED) over(partition by t1.ITEMCODE,t1.ITEMDES),sum(t2.ITEMUSED) over(partition by decode(length(t1.ITEMCODE),4,t1.ITEMCODE,substr(t1.ITEMCODE,1,length(t1.ITEMCODE)-2))))
from (
select '1010' as ITEMCODE,'bi' as ITEMDES from dual
union all
select '101001' as ITEMCODE,'yuanzibi' as ITEMDES from dual
union all
select '101002' as ITEMCODE,'qianbi' as ITEMDES from dual
union all
select '101003' as ITEMCODE,'gangbi' as ITEMDES from dual
)t1,
(
select '101001' as ITEMCODE,2 as ITEMUSED,'2007-02-05' as USEDTIME from dual
union all
select '101001' as ITEMCODE,4 as ITEMUSED,'2007-02-09' as USEDTIME from dual
union all
select '101002' as ITEMCODE,3 as ITEMUSED,'2007-02-08' as USEDTIME from dual
union all
select '101003' as ITEMCODE,7 as ITEMUSED,'2007-02-04' as USEDTIME from dual
)t2
where t1.ITEMCODE = t2.ITEMCODE(+);
============================result=================================
ITEMCODE ITEMDES NVL(SUM(T2.ITEMUSED)OVER(PARTI
-------- -------- ------------------------------
1010 bi 16
101001 yuanzibi 6
101002 qianbi 3
101003 gangbi 7
from a left outer join
b on b.itemcode like a.itemcode||'%'
group by a.itemcode,a.itemdes;
select a.itemcode,a.itemdes,sum(b.itemused)
from a left outer join
b on b.itemcode like a.itemcode||'%'
group by a.itemcode,a.itemdes;