本帖最后由 tjshalive 于 2012-11-13 19:49 编辑
==============两个表全外连
selectNVL(t1.center_code,T2.CENTER_CODE) CENTER_CODE,
NVL(t1.Cost_Mon,T2.COST_MON) COST_MON,
T1.COST_AMOUNT,--中心工资
T2.COST_AMOUNT--中心保险
from
BPO_CENTER_COST_HUMAN_SPLITt1
FULL JOIN
BPO_CENTER_COST_INSURE_SPLIT T2
ON T1.CENTER_CODE=T2.CENTER_CODE
AND T1.COST_MON=T2.COST_MON
未命名1.jpg (63.77 KB, 下载次数: 0)
下载附件
2012-11-13 19:38 上传
=============两个表分组后全外连
selectNVL(t1.center_code,T2.CENTER_CODE) CENTER_CODE,
NVL(t1.Cost_Mon,T2.COST_MON) COST_MON,
T1.CENTER_PAY,--中心工资
T2.CENTER_COST_INSURE--中心保险from (SELECT T.CENTER_CODE,T.COST_MON,SUM(T.COST_AMOUNT) CENTER_PAY
FROM BPO_CENTER_COST_HUMAN_SPLIT T GROUP BY T.CENTER_CODE,T.COST_MON )t1
FULL JOIN
(SELECT T.CENTER_CODE,T.COST_MON,SUM(T.COST_AMOUNT) CENTER_COST_INSURE FROM BPO_CENTER_COST_INSURE_SPLIT T GROUP BY T.CENTER_CODE,T.COST_MON ) T2ON T1.CENTER_CODE=T2.CENTER_CODE AND T1.COST_MON=T2.COST_MON
未命名2.jpg (88.73 KB, 下载次数: 1)
下载附件
2012-11-13 19:39 上传
============三个表分组后外连接
SELECT
NVL(V0.center_code,T3.CENTER_CODE) CENTER_CODE,
NVL(V0.Cost_Mon,T3.COST_MON) COST_MON,
V0.CENTER_PAY,
V0.CENTER_COST_INSURE,
T3.SUN_INSURE FROM
(selectNVL(t1.center_code,T2.CENTER_CODE) CENTER_CODE,
NVL(t1.Cost_Mon,T2.COST_MON) COST_MON,
T1.CENTER_PAY,--中心工资
T2.CENTER_COST_INSURE--中心保险
from
(SELECT T.CENTER_CODE,T.COST_MON,SUM(T.COST_AMOUNT) CENTER_PAY FROM BPO_CENTER_COST_HUMAN_SPLIT T GROUP BY T.CENTER_CODE,T.COST_MON )t1
FULL JOIN
(SELECT T.CENTER_CODE,T.COST_MON,SUM(T.COST_AMOUNT) CENTER_COST_INSURE FROM BPO_CENTER_COST_INSURE_SPLIT T GROUP BY T.CENTER_CODE,T.COST_MON ) T2ON T1.CENTER_CODE=T2.CENTER_CODE AND T1.COST_MON=T2.COST_MON) V0
FULL JOIN
(SELECT T.CENTER_CODE,T.COST_MON,SUM(T.MONTH_VALUE) SUN_INSURE FROM BPO_SUN_INSURE T GROUP BY T.CENTER_CODE,T.COST_MON ) T3ON V0.CENTER_CODE=T3.CENTER_CODE AND V0.COST_MON=T3.COST_MON
未命名3.jpg (135.09 KB, 下载次数: 2)
下载附件
2012-11-13 19:39 上传
未命名4.jpg (123.09 KB, 下载次数: 0)
下载附件
2012-11-13 19:40 上传
未命名5.jpg (138.02 KB, 下载次数: 1)
下载附件
2012-11-13 19:40 上传
未命名6.jpg (28.73 KB, 下载次数: 2)
下载附件
2012-11-13 19:40 上传
为什么全外连接会扫描表这么多次?
目的就是把多张表分组后，汇总一下数据，是不是SQL写的有问题？
|
