这个sql应该怎样写

Question

根据业务逻辑，中间需要产生三个临时表t1,t2,t3，结构完全相同
(
ID      CHAR,
QTY1   NUMBER,
QTY2   NUMBER
)
应该怎样连接才能实现下面的结果？
测试数据
t1:
S001            100             200
S002            300             400
S003            500             600
t2:
S001            80              90
S002            60              50
S004            71              72
t3:
S001            110             120
S005            130             140
抽出数据：
(
ID      :t1.ID 或t2.ID或t3.ID
QTY1   :t1.QTY1,
QTY2   :t1.QTY2,
QTY3   :t2.QTY1,
QTY4   :t2.QTY2,
QTY5   :t3.QTY1,
QTY6   :t3.QTY2
)
抽出的结果应为：
S001     100     200     80     90     110     120
S002     300     400     60     50       0       0
S003     500     600      0      0       0       0
S004       0       0     71     72       0       0
S005       0       0      0      0     130     140

Answer 1

select t4.id,nvl(t1.qty1,0) qty1,nvl(t1.qty2,0) qty2,
  nvl(t2.qty1,0) qty3,nvl(t2.qty2,0) qty4,
  nvl(t3.qty1,0) qty5,nvl(t3.qty2,0) qty6 from 
  (select id from t1 union select id from t2 union select id from t3)t4
  left join t1 on t1.id=t4.id
  left join t2 on t4.id=t2.id 
  left join t3 on t3.id=t4.id
  order by id

Answer 2

呵呵，有遇见你了
SQL开发，很好，很强大

Answer 3

select t1.id, nvl(t1.qty1,0),nvl(t1.qty2,0),nvl(t2.qty1,0),nvl(t2.qty2,0),nvl(t3.qty1,0),nvl(t3.qty2,0) 
from t1,t2,t3
where t1.id = t2.id(+)
and t1.id = t3.id(+)
union
select t2.id, nvl(t1.qty1,0),nvl(t1.qty2,0),nvl(t2.qty1,0),nvl(t2.qty2,0),nvl(t3.qty1,0),nvl(t3.qty2,0)  
from t1,t2,t3
where t1.id(+) = t2.id
and t2.id = t3.id(+)
union
select t3.id, nvl(t1.qty1,0),nvl(t1.qty2,0),nvl(t2.qty1,0),nvl(t2.qty2,0),nvl(t3.qty1,0),nvl(t3.qty2,0) 
from t1,t2,t3
where t1.id(+) = t3.id
and t1.id(+) = t3.id;

Answer 4

select nvl(nvl(t1.id,t2.id),t3.id),nvl(t1.QTY1,0),nvl(t1.QTY2,0),nvl(t2.QTY1,0),nvl(t2.QTY2,0),nvl(t3.QTY1,0),nvl(t3.QTY2,0) from t1 full join t2 on t1.id = t2.id full join t3 on t1.id = t3.id;

Answer 5

要排序的话：
select nvl(nvl(t1.id,t2.id),t3.id),nvl(t1.QTY1,0),nvl(t1.QTY2,0),nvl(t2.QTY1,0),nvl(t2.QTY2,0),nvl(t3.QTY1,0),nvl(t3.QTY2,0) from t1 full join t2 on t1.id = t2.id full join t3 on t1.id = t3.id order by 1;

Answer 6

哈哈4楼的full join对3表以上会有问题
若t3中有id不存在于t1但存在于t2的记录
结果里不会将其与t2的那条记录连接，而是单独显示
会有很多重复

Answer 7

可以加个distinct 来试试!

Answer 8

这个执行结果不正确：
SELECT NVL(T1.PRODUCTIONINDICATENO,NVL(T2.PRODUCTIONINDICATENO,T3.PRODUCTIONINDICATENO)) AS PRODUCTIONINDICATENO
,NVL(T1.RESULTQTY,0) AS INSTOCKRESULTQTY,NVL(T1.IPC_RESULTQTY,0) AS INSTOCKIPC_RESULTQTY
,NVL(T2.RESULTQTY,0) AS OUTSTOCKRESULTQTY,NVL(T2.IPC_RESULTQTY,0) AS OUTSTOCKIPC_RESULTQTY
,NVL(T3.RESULTQTY,0) AS OUTSTOCKENTERQTY,NVL(T3.IPC_RESULTQTY,0) AS OUTSTOCKIPC_ENTERQTY 
FROM
 (SELECT    PRODUCTIONINDICATENO  ,SUM(RESULTQTY) AS RESULTQTY  ,SUM(IPC_RESULTQTY) AS IPC_RESULTQTY
  FROM FA01_PRODUCTIONRESULT  WHERE LOTNO =  'LOTNO12380'    AND PROCCD =  '850'
  GROUP BY PRODUCTIONINDICATENO  ) T1
  FULL JOIN 
 (SELECT    PRODUCTIONINDICATENO  ,SUM(RESULTQTY) AS RESULTQTY  ,SUM(IPC_RESULTQTY) AS IPC_RESULTQTY
  FROM FA01_PRODUCTIONRESULT  WHERE LOTNO =  'LOTNO12380'    AND PROCCD =  '800'
  GROUP BY PRODUCTIONINDICATENO  ) T2
 ON T1.PRODUCTIONINDICATENO = T2.PRODUCTIONINDICATENO  
FULL JOIN  (SELECT    PRODUCTIONINDICATENO  ,SUM(RESULTQTY) AS RESULTQTY  ,SUM(IPC_RESULTQTY) AS IPC_RESULTQTY
  FROM FD02_LOTLABEL  WHERE LOTNO =  'LOTNO12380'    AND DELETEFLG = '0'    AND MODIFYFLG = '0'
  GROUP BY PRODUCTIONINDICATENO  ) T3
 ON T1.PRODUCTIONINDICATENO = T3.PRODUCTIONINDICATENO ORDER BY 1
测试数据：
t1
PRODUCTIONINDICATENO RESULTQTY IPC_RESULTQTY
PROIND32 930 452
PROIND31 1499 1410
t2
PRODUCTIONINDICATENO RESULTQTY IPC_RESULTQTY
PROIND32 8000 3265
PROIND31 1499 7430
PROIND33 900 6320
t3
PRODUCTIONINDICATENO RESULTQTY IPC_RESULTQTY
PROINO34 1000 1
PROINO32 1500 1
PROINO31 1000 1
SQL执行结果：
PRODUCTIONINDICATENO INSTOCKRESULTQTY INSTOCKIPC_RESULTQTY OUTSTOCKRESULTQTY OUTSTOCKIPC_RESULTQTY OUTSTOCKENTERQTY OUTSTOCKIPC_ENTERQTY
PROIND31 1499 1410 1499 7430 0 0
PROIND32 930 452 8000 3265 0 0
PROIND33 0 0 900 6320 0 0
PROINO31 0 0 0 0 1000 1
PROINO32 0 0 0 0 1500 1
PROINO34 0 0 0 0 1000 1

Answer 9

这两个应该可以：SELECT t.PRODUCTIONINDICATENO,NVL(t1.RESULTQTY,0) INSTOCKRESULTQTY,NVL(t1.IPC_RESULTQTY,0) INSTOCKIPC_RESULTQTY,NVL(t2.RESULTQTY,0) OUTSTOCKRESULTQTY,NVL(t2.IPC_RESULTQTY,0) OUTSTOCKIPC_RESULTQTY,NVL(t3.RESULTQTY,0) OUTSTOCKENTERQTY,NVL(t3.IPC_RESULTQTY,0) OUTSTOCKIPC_ENTERQTY
FROM (SELECT PRODUCTIONINDICATENO FROM t1 UNION SELECT PRODUCTIONINDICATENO FROM t2 UNION SELECT PRODUCTIONINDICATENO FROM t3)t 
LEFT JOIN t1 ON t1.PRODUCTIONINDICATENO=t.PRODUCTIONINDICATENO LEFT JOIN t2 ON t.PRODUCTIONINDICATENO=t2.PRODUCTIONINDICATENO LEFT JOIN t3 ON t3.PRODUCTIONINDICATENO=t.PRODUCTIONINDICATENO 
ORDER BY 1SELECT NVL(t.PRODUCTIONINDICATENO,t3.PRODUCTIONINDICATENO),NVL(col1,0),NVL(col2,0),NVL(col3,0),NVL(col4,0),NVL(t3.RESULTQTY,0) ,NVL(t3.IPC_RESULTQTY,0) FROM 
(SELECT NVL(t1.PRODUCTIONINDICATENO,t2.PRODUCTIONINDICATENO) PRODUCTIONINDICATENO,
NVL(t1.RESULTQTY,0) col1,NVL(t1.IPC_RESULTQTY,0) col2,
NVL(t2.RESULTQTY,0) col3,NVL(t2.IPC_RESULTQTY,0) col4
FROM t1 FULL JOIN t2 ON t1.PRODUCTIONINDICATENO = t2.PRODUCTIONINDICATENO) t
FULL JOIN t3 ON t.PRODUCTIONINDICATENO = t3.PRODUCTIONINDICATENO 
ORDER BY 1;
---------------------
注意，你的ID字段t1、t2中..ND，而t3中是..N0

Answer 10

我使用了wildwave的方法，好理解，就是代码太长。嵌套了好几层
你的方法有空再测试吧。要不时间花得就太多了。进度赶不上了。
暂时结帖，谢谢大家

Answer 11

9楼的代码中，将t1，t2两表合并
select nvl(t1.PRODUCTIONINDICATENO,t3.PRODUCTIONINDICATENO)PRODUCTIONINDICATENO,
     nvl(t1.INSTOCKRESULTQTY,0)INSTOCKRESULTQTY,nvl(t1.INSTOCKIPC_RESULTQTY,0)INSTOCKIPC_RESULTQTY,
     nvl(t1.OUTSTOCKRESULTQTY,0)OUTSTOCKRESULTQTY,nvl(t1.OUTSTOCKIPC_RESULTQTY,0)OUTSTOCKIPC_RESULTQTY,
     nvl(t3.RESULTQTY,0)OUTSTOCKENTERQTY,nvl(t3.IPC_RESULTQTY,0)OUTSTOCKIPC_ENTERQTY
from 
    (select PRODUCTIONINDICATENO,SUM(decode(PROCCD,'850',RESULTQTY)) AS INSTOCKRESULTQTY,
      sum(decode(PROCCD,'850',IPC_RESULTQTY)) AS INSTOCKIPC_RESULTQTY,
      sum(decode(PROCCD,'800',RESULTQTY)) AS OUTSTOCKRESULTQTY,
      sum(decode(PROCCD,'800',IPC_RESULTQTY)) AS OUTSTOCKIPC_RESULTQTY
    from FA01_PRODUCTIONRESULT  WHERE LOTNO =  'LOTNO12380' where PROCCD = '850' or PROCCD ='800'
    GROUP BY PRODUCTIONINDICATENO)t1 
full join 
    (SELECT    PRODUCTIONINDICATENO  ,SUM(RESULTQTY) AS RESULTQTY  ,SUM(IPC_RESULTQTY) AS IPC_RESULTQTY
      FROM FD02_LOTLABEL  WHERE LOTNO =  'LOTNO12380'    AND DELETEFLG = '0'    AND MODIFYFLG = '0'
      GROUP BY PRODUCTIONINDICATENO  ) T3
on t1.PRODUCTIONINDICATENO=t3.PRODUCTIONINDICATENO