Tabel_A结构如下:GOODS_CD TRANS_TYPE FROM TO QTY
GA 01 A 100
GA 02 B 600
GA 03 C D 350
GA 02 C 400
Table_B结构如下:
GOODS Location INV_QTY
GA A 1100
GA B 0
GA C 150
GA D 500Table_A是物品转移数量,Table_B是物品在各个地方的库存初期数量,我想得到的是物品在经过转移后在各个地方的库存数量。
GOODS Location Inv_Qty
GA A 1000 (1100 - 100 )
GA B 600 (0 + 600)
GA C 200 (150 + 400 - 350 )
GA D 850 (500 + 350 ) SQL怎样写?
GA 01 A 100
GA 02 B 600
GA 03 C D 350
GA 02 C 400
Table_B结构如下:
GOODS Location INV_QTY
GA A 1100
GA B 0
GA C 150
GA D 500Table_A是物品转移数量,Table_B是物品在各个地方的库存初期数量,我想得到的是物品在经过转移后在各个地方的库存数量。
GOODS Location Inv_Qty
GA A 1000 (1100 - 100 )
GA B 600 (0 + 600)
GA C 200 (150 + 400 - 350 )
GA D 850 (500 + 350 ) SQL怎样写?
解决方案 »
- OC4J问题,困扰我好长时间了,大家帮忙啊
- oracle
- PL/SQLPLUS DEVELOPER中的宏,oracle中分区与索引,创建表,分区,索引时的一些参数设置
- 简单查询语句~~100分
- 急救~~~混装了oracle 8.17和8.16两个版本,启动8.16,报错:ORA-01092 ORACLE instance!
- 如何修改HOST名?
- 求解决方案**
- 最后一点啦,求一触发器
- 回滚段问题
- ERROR Unknown error when running consumer: (kafka.tools.ConsoleConsumer$)
- 这个条件的查询语句怎么写吖? 难道要用循环?(续)
- oracle 做查询select操作 怎样防止DDL语句修改表结构
GOODS_CD TRANS_TYPE FROM TO QTY INV
GA 01 A 100 1000 (1100 -100)
GA 02 B 600 600 (0 + 600 )
GA 03-1 C 350 -200 (150 - 350 )
GA 03-2 D 350 850 (500 + 350 )
GA 02 C 400 200 (400 - 200 )
trans_type 是01的,只有from列有值。
trans_type 是03的,from列和to列都有值。
SQL> WITH t AS (
2 SELECT 'GA' goods_cd,'01' trans_type,'A' lform,NULL lto,100 qty FROM dual UNION ALL
3 SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'B' lto,600 qty FROM dual UNION ALL
4 SELECT 'GA' goods_cd,'03' trans_type,'C' lform,'D' lto,350 qty FROM dual UNION ALL
5 SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'C' lto,400 qty FROM dual
6 )
7 , tb AS (
8 SELECT 'GA' goods_cd,'A' location_cd,1100 inv_qty FROM dual UNION ALL
9 SELECT 'GA' goods_cd,'B' location_cd,0 inv_qty FROM dual UNION ALL
10 SELECT 'GA' goods_cd,'C' location_cd,150 inv_qty FROM dual UNION ALL
11 SELECT 'GA' goods_cd,'D' location_cd,500 inv_qty FROM dual
12 )
13 SELECT n.goods_cd,
14 n.location_cd,
15 n.qty + tb.inv_qty qty
16 FROM (SELECT m.goods_cd,
17 m.location_cd,
18 SUM(m.qty) qty
19 FROM (SELECT t1.goods_cd,
20 t1.trans_type,
21 t1.lform location_cd,
22 -t1.qty qty
23 FROM t t1
24 WHERE t1.lform IS NOT NULL
25 UNION ALL
26 SELECT t2.goods_cd,
27 t2.trans_type,
28 t2.lto,
29 t2.qty
30 FROM t t2
31 WHERE t2.lto IS NOT NULL) m
32 GROUP BY m.goods_cd,
33 m.location_cd) n
34 INNER JOIN tb ON n.goods_cd = tb.goods_cd
35 AND n.location_cd = tb.location_cd
36 ;GOODS_CD LOCATION_CD QTY
-------- ----------- ----------
GA B 600
GA A 1000
GA C 200
GA D 850
WITH t AS (
SELECT 'GA' goods_cd,'01' trans_type,'A' lform,NULL lto,100 qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'B' lto,600 qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'03' trans_type,'C' lform,'D' lto,350 qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'C' lto,400 qty FROM dual
)
, tb AS (
SELECT 'GA' goods_cd,'A' location_cd,1100 inv_qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'B' location_cd,0 inv_qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'C' location_cd,150 inv_qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'D' location_cd,500 inv_qty FROM dual
)
-- 下面是实际SQL
select tb.goods_cd,tb.location_cd,tb.inv_qty - nvl(a.s,0) + nvl(b.s,0)
from tb,
-- 各仓库的出库量
(select goods_cd,lform,sum(qty) s from t where lform is not null group by goods_cd,lform) A,
-- 各仓库的入库量
(select goods_cd,lto,sum(qty) s from t where lto is not null group by goods_cd,lto) B
where tb.goods_cd = a.goods_cd(+)
and tb.location_cd = a.lform(+)
and tb.goods_cd = b.goods_cd(+)
and tb.location_cd = b.lto(+)
order by goods_cd,location_cd ;
WITH t AS (
SELECT 'GA' goods_cd,'01' trans_type,'A' lform,NULL lto,100 qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'B' lto,600 qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'03' trans_type,'C' lform,'D' lto,350 qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'C' lto,400 qty FROM dual
)
, tb AS (
SELECT 'GA' goods_cd,'A' location_cd,1100 inv_qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'B' location_cd,0 inv_qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'C' location_cd,150 inv_qty FROM dual UNION ALL
SELECT 'GA' goods_cd,'D' location_cd,500 inv_qty FROM dual
)
-- 下面是对应的SQL,需求校复杂,没有想到简方的答案
select a.goods_cd,
a.trans_type,
case when qty_t > 0 then '' else a.location_cd end lform,
case when qty_t > 0 then a.location_cd else '' end lto,
a.qty,
sum(qty_t) over(partition by a.location_cd order by a.rn) + tb.inv_qty
from
(select goods_cd,trans_type||nvl2(lto,'-1','') trans_type,lform location_cd,qty,-qty qty_t,rn from (select t.*,rownum rn from t) where lform is not null
union
select goods_cd,trans_type||nvl2(lform,'-2',''),lto,qty,qty qty_t,rn from (select t.*,rownum rn from t) where lto is not null
order by rn) A,
tb
where a.location_cd = tb.location_cd
order by rn,trans_type ;
2 SELECT 'GA' goods_cd,'01' trans_type,'A' lform,NULL lto,100 qty FROM DUAL UNION ALL
3 SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'B' lto,600 qty FROM DUAL UNION ALL
4 SELECT 'GA' goods_cd,'03' trans_type,'C' lform,'D' lto,350 qty FROM DUAL UNION ALL
5 SELECT 'GA' goods_cd,'02' trans_type,NULL lform,'C' lto,400 qty FROM DUAL
6 ) ,tb AS (
7 SELECT 'GA' goods_cd,'A' location_cd,1100 inv_qty FROM DUAL UNION ALL
8 SELECT 'GA' goods_cd,'B' location_cd,0 inv_qty FROM DUAL UNION ALL
9 SELECT 'GA' goods_cd,'C' location_cd,150 inv_qty FROM DUAL UNION ALL
10 SELECT 'GA' goods_cd,'D' location_cd,500 inv_qty FROM DUAL
11 )
12 SELECT m.goods_cd,
13 m.trans_type,
14 m.location_cd,
15 ABS(m.qty) qty,
16 SUM(m.qty + DECODE(m.rn,1,m.inv_qty,0)) OVER(PARTITION BY m.location_cd ORDER BY m.rn) inv_qty2
17 FROM (SELECT n.*,
18 ROW_NUMBER() OVER(PARTITION BY n.location_cd ORDER BY n.qty) rn,
19 tb.inv_qty
20 FROM (
21 SELECT t1.goods_cd,
22 DECODE(t1.lto, NULL, t1.trans_type, t1.trans_type || '-1') trans_type,
23 t1.lform location_cd,
24 -t1.qty qty
25 FROM t t1
26 WHERE t1.lform IS NOT NULL
27 UNION ALL
28 SELECT t2.goods_cd,
29 DECODE(t2.lform, NULL, t2.trans_type, t2.trans_type || '-2') trans_type,
30 t2.lto,
31 t2.qty
32 FROM t t2
33 WHERE t2.lto IS NOT NULL) n
34 INNER JOIN tb ON n.goods_cd = tb.goods_cd
35 AND n.location_cd = tb.location_cd
36 ) m
37 ORDER BY m.goods_cd,m.trans_type
38 ;GOODS_CD TRANS_TYPE LOCATION_CD QTY INV_QTY2
-------- ---------- ----------- ---------- ----------
GA 01 A 100 1000
GA 02 B 600 600
GA 02 C 400 200
GA 03-1 C 350 -200
GA 03-2 D 350 850