请教SQL

Question

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怎样写?           

Answer 1

表设计不规范，建议建个临时表将GA 03 C D 350这中记录拆分成两条记录，这样再用inner join就可以了 

Answer 2

还有一种结果如下，SQL又应该如何写？
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 )

Answer 3

trans_type 是02的，只有to列有值。
trans_type 是01的，只有from列有值。
trans_type 是03的，from列和to列都有值。

Answer 4

trans_type是03的行，我希望分拆成两行，并且两行的trans_type分别改变为03-1和03-2

Answer 5

思路就是把03的数据拆分成两条记录
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

Answer 6

我引用了5楼的测试数据，感谢5楼所做的工作。我的思路是先求出各仓库的出库总数量及入库总数量，然后再用(原始库存-出库总数量+入库总数量)来得到结果。-- 借用了5楼的测试数据
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 ;

Answer 7

3楼的答案-- 借用了5楼的测试数据
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 ;

Answer 8

3#，我的写法：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  ) ,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