sql 使CPU使用100%，棘手！

askgyliu

我简单的测试数据如下：

/home/db2inst/sql_performance/test2 > db2 -v "select * from PAR_CUST_CUST_GRP_ASSOC_200710"
select * from PAR_CUST_CUST_GRP_ASSOC_200710

CUST_ID             CUST_GROUP_ID
------------------- ----------------------
              1001.                     1.
              1002.                     1.
              2001.                     2.

  3 record(s) selected.


/home/db2inst/sql_performance/test2 > db2 -v "select * from CUST_CHURN_INFO_200710"
select * from CUST_CHURN_INFO_200710

CUST_ID             ON_NET_TIME        LEAVE_DATE                 PRE_M_ARPU_D         PRE_M_ARPU         NOW_ARPU
------------------- ------------------ -------------------------- -------------------- ------------------ ------------------
              1001.               4.00 2007-12-13-13.03.59.523346 A                                  7.10               3.30
              1002.               4.00 2007-12-13-13.03.59.542610 A                                  5.20              31.40
              1003.               4.00 2007-12-13-13.03.59.572608 A                                  1.30              37.60
              1004.               9.00 2007-12-13-13.03.59.592597 A                                 17.40              23.90
              2001.               4.00 2007-12-13-13.03.59.612568 A                                 13.50              17.30

  5 record(s) selected.


/home/db2inst/sql_performance/test2 > db2 -v "select * from CUST_CCP_INFO_200710"
select * from CUST_CCP_INFO_200710

CUST_ID             M                  CLV
------------------- ------------------ ------------------
              1001.              21.00               0.21
              1002.              11.00               0.11
              1003.              31.00               0.31
              2001.              41.00               0.41

  4 record(s) selected.


/home/db2inst/sql_performance/test2 > db2 -v "select * from V_PRE_EXP_GROUP_CUST1"
select * from V_PRE_EXP_GROUP_CUST1

CUST_ID
-------------------
              2001.

  1 record(s) selected.

askgyliu

更有趣的是，我把LZ的SQL再稍微改一下：

把  最后一个JOIN条件 on a1.group_id=a3.group_id and a1.pre_m_arpu_d= a3.pre_m_arpu_d
改成 on a2.group_id=a3.group_id and a2.pre_m_arpu_d= a3.pre_m_arpu_d

得到的结果就跟我的是版本是一样的了。迷惑人的FULL OUTER JOIN。

/home/db2inst/sql_performance/test2 > db2 -tvf t2.sql
select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id, coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d) as pre_m_arpu_d, a2.ADD_SUM_NOW_ARPU, a3.LOSS_SUM_NOW_ARPU from ( select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU) as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU from V_PRE_EXP_GROUP_CUST1 a inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as ADD_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU from CUST_CHURN_INFO_200710 base where on_net_time=4) cust on cust.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full outer join ( select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 a inner join (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU from CUST_CHURN_INFO_200710 b where b.LEAVE_DATE <= '3000-11-30 00:00:00') churn on churn.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by a.cust_group_id,churn.pre_m_arpu_d ) a3 on a2.group_id=a3.group_id and a2.pre_m_arpu_d= a3.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                34.70                             34.70
                    2. A                                                17.30                             17.30

  2 record(s) selected.

myfriend2010

你改过肯定就不符合我的业务逻辑了!呵呵!askgyliu的专研精神难能可贵!

原帖由 askgyliu 于 2007-12-13 14:17 发表
更有趣的是，我把LZ的SQL再稍微改一下：

把  最后一个JOIN条件 on a1.group_id=a3.group_id and a1.pre_m_arpu_d= a3.pre_m_arpu_d
改成 on a2.group_id=a3.group_id and a2.pre_m_arpu_d= a3.pre_m_arpu_d

得到的结果就跟我的是版本是一样的了。迷惑人的FULL OUTER JOIN。

/home/db2inst/sql_performance/test2 > db2 -tvf t2.sql
select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id, coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d) as pre_m_arpu_d, a2.ADD_SUM_NOW_ARPU, a3.LOSS_SUM_NOW_ARPU from ( select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU) as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU from V_PRE_EXP_GROUP_CUST1 a inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as ADD_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU from CUST_CHURN_INFO_200710 base where on_net_time=4) cust on cust.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full outer join ( select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 a inner join (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU from CUST_CHURN_INFO_200710 b where b.LEAVE_DATE  

diablo2

你们继续。
看着那么多字符，头痛中……

askgyliu

再变个魔术。

不要改变LZ的SQL，就在V_PRE_EXP_GROUP_CUST1中多加一行：

/home/db2inst/sql_performance/test2 > db2 -stvf t1.sql
select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id, coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d) as pre_m_arpu_d, a2.ADD_SUM_NOW_ARPU, a3.LOSS_SUM_NOW_ARPU from ( select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU) as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU from V_PRE_EXP_GROUP_CUST1 a inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as ADD_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU from CUST_CHURN_INFO_200710 base where on_net_time=4) cust on cust.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full outer join ( select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 a inner join (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU from CUST_CHURN_INFO_200710 b where b.LEAVE_DATE <= '3000-11-30 00:00:00') churn on churn.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by a.cust_group_id,churn.pre_m_arpu_d ) a3 on a1.group_id=a3.group_id and a1.pre_m_arpu_d= a3.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                    -                             34.70
                    2. A                                                17.30                             17.30
                    1. A                                                34.70                                 -

  3 record(s) selected.


/home/db2inst/sql_performance/test2 > cat insert.sql


INSERT INTO PAR_CUST_CUST_GRP_ASSOC_200710 VALUES(1001,1);
INSERT INTO PAR_CUST_CUST_GRP_ASSOC_200710 VALUES(1002,1);
INSERT INTO PAR_CUST_CUST_GRP_ASSOC_200710 VALUES(2001,2);

INSERT INTO CUST_CHURN_INFO_200710 VALUES(1001, 4, current timestamp, 'A', 7.1, 3.3);
INSERT INTO CUST_CHURN_INFO_200710 VALUES(1002, 4, current timestamp, 'A', 5.2, 31.4);
INSERT INTO CUST_CHURN_INFO_200710 VALUES(1003, 4, current timestamp, 'A', 1.3, 37.6);
INSERT INTO CUST_CHURN_INFO_200710 VALUES(1004, 9, current timestamp, 'A', 17.4, 23.9);
INSERT INTO CUST_CHURN_INFO_200710 VALUES(2001, 4, current timestamp, 'A', 13.5, 17.3);

INSERT INTO CUST_CCP_INFO_200710 VALUES(1001, 21, 0.21);
INSERT INTO CUST_CCP_INFO_200710 VALUES(1002, 11, 0.11);
INSERT INTO CUST_CCP_INFO_200710 VALUES(1003, 31, 0.31);
INSERT INTO CUST_CCP_INFO_200710 VALUES(2001, 41, 0.41);

<在这里做一个INSERT加一行>
INSERT INTO V_PRE_EXP_GROUP_CUST1 VALUES(2001);
/home/db2inst/sql_performance/test2 > db2 -v "INSERT INTO V_PRE_EXP_GROUP_CUST1 VALUES(1001)"
INSERT INTO V_PRE_EXP_GROUP_CUST1 VALUES(1001)
DB20000I  The SQL command completed successfully.

/home/db2inst/sql_performance/test2 > db2 -stvf t1.sql
select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id, coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d) as pre_m_arpu_d, a2.ADD_SUM_NOW_ARPU, a3.LOSS_SUM_NOW_ARPU from ( select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU) as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU from V_PRE_EXP_GROUP_CUST1 a inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as ADD_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU from CUST_CHURN_INFO_200710 base where on_net_time=4) cust on cust.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full outer join ( select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 a inner join (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU from CUST_CHURN_INFO_200710 b where b.LEAVE_DATE <= '3000-11-30 00:00:00') churn on churn.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by a.cust_group_id,churn.pre_m_arpu_d ) a3 on a1.group_id=a3.group_id and a1.pre_m_arpu_d= a3.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                34.70                             34.70
                    2. A                                                17.30                             17.30

  2 record(s) selected.


奇迹出现了。本来LZ有三行的结果变成了两行。而我的版本的结果还是没变。

/home/db2inst/sql_performance/test2 > db2 -stvf tt.sql
select  b.cust_group_id as group_id, aa.pre_m_arpu_d, sum(case when aa.on_net_time=4 then aa.NOW_ARPU end) as ADD_SUM_NOW_ARPU , sum(case when aa.leave_date<='3000-11-30 00:00:00' then aa.NOW_ARPU end) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id left outer join V_PRE_EXP_GROUP_CUST1 a on b.cust_id = a.cust_id where (a.cust_id is not null or aa.on_net_time=4 or aa.LEAVE_DATE <= '3000-11-30 00:00:00') group by b.cust_group_id,aa.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                34.70                             34.70
                    2. A                                                17.30                             17.30

  2 record(s) selected.

再问LZ一个问题：你期望的最后结果是GROUP+PRE_M_ARPU_D是唯一的，还是非唯一的？

[ 本帖最后由 askgyliu 于 2007-12-13 14:39 编辑 ]

askgyliu

这里只是证明了有三个TABLES在做FULL OUTER JOIN的话，最后结果是不可预测的。

但是DB2会顺着SEQUENCE来JOIN，而最终结果是可能很出人意料的。

askgyliu

原帖由 myfriend2010 于 2007-12-13 13:51 发表
PAR_CUST_CUST_GRP_ASSOC_200710 中CUST_ID不是唯一的!而且重复性很大,在这个表中只有cust和cust_group_id组合才是唯一索引!

那其他几个TABLES呢？CUST_ID是唯一的吗？

askgyliu

先把那个会导致LZ的结果跟我的版本一样的数据从V_PRE_EXP_GROUP_CUST1中拿掉：

/home/db2inst/sql_performance/test2 > db2 -v "select * from V_PRE_EXP_GROUP_CUST1"
select * from V_PRE_EXP_GROUP_CUST1

CUST_ID
-------------------
              2001.

  1 record(s) selected.


在PAR_CUST_CUST_GRP_ASSOC_200710中多INSERT一行，让CUST_ID 1001 有两个CUST_GROUP_ID：

/home/db2inst/sql_performance/test2 > db2 -v "select * from PAR_CUST_CUST_GRP_ASSOC_200710"
select * from PAR_CUST_CUST_GRP_ASSOC_200710

CUST_ID             CUST_GROUP_ID
------------------- ----------------------
              1001.                     1.
              1002.                     1.
              2001.                     2.
              1001.                     2.

  4 record(s) selected.

再来执行LZ的SQL：

/home/db2inst/sql_performance/test2 > db2 -stvf t1.sql
select coalesce(a1.group_id,a2.group_id,a3.group_id) as group_id, coalesce(a1.pre_m_arpu_d,a2.pre_m_arpu_d,a3.pre_m_arpu_d) as pre_m_arpu_d, a2.ADD_SUM_NOW_ARPU, a3.LOSS_SUM_NOW_ARPU from ( select b.cust_group_id as group_id, aa.pre_m_arpu_d,sum(aa.PRE_M_ARPU) as arpu,sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as EXIST_CUST_C, sum(aa.NOW_ARPU) as EXIST_SUM_NOW_ARPU from V_PRE_EXP_GROUP_CUST1 a inner join PAR_CUST_CUST_GRP_ASSOC_200710 b on b.cust_id = a.cust_id inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by b.cust_group_id,aa.pre_m_arpu_d ) a1 full outer join (select b.cust_group_id as group_id,sum(cust.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(b.cust_id) as ADD_CUST_C, cust.pre_m_arpu_d, sum(cust.NOW_ARPU) as ADD_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join (select base.cust_id,base.pre_m_arpu_d,base.NOW_ARPU,base.PRE_M_ARPU from CUST_CHURN_INFO_200710 base where on_net_time=4) cust on cust.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id group by b.cust_group_id,cust.pre_m_arpu_d ) a2 on a1.group_id=a2.group_id and a1.pre_m_arpu_d= a2.pre_m_arpu_d full outer join ( select a.cust_group_id as group_id,sum(churn.PRE_M_ARPU) as arpu, sum(cc.m) as m,sum(cc.CLV) as clv, count(a.cust_id) as LOSS_CUST_C, churn.pre_m_arpu_d, sum(churn.NOW_ARPU) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 a inner join (select b.cust_id as cust_id,b.pre_m_arpu_d,b.PRE_M_ARPU,b.NOW_ARPU from CUST_CHURN_INFO_200710 b where b.LEAVE_DATE <= '3000-11-30 00:00:00') churn on churn.cust_id=a.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=a.cust_id group by a.cust_group_id,churn.pre_m_arpu_d ) a3 on a1.group_id=a3.group_id and a1.pre_m_arpu_d= a3.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                    -                             34.70
                    2. A                                                20.60                             20.60
                    1. A                                                34.70                                 -

  3 record(s) selected.


再来执行我的版本的SQL：

/home/db2inst/sql_performance/test2 > db2 -stvf tt.sql
select  b.cust_group_id as group_id, aa.pre_m_arpu_d, sum(case when aa.on_net_time=4 then aa.NOW_ARPU end) as ADD_SUM_NOW_ARPU , sum(case when aa.leave_date<='3000-11-30 00:00:00' then aa.NOW_ARPU end) as LOSS_SUM_NOW_ARPU from PAR_CUST_CUST_GRP_ASSOC_200710 b inner join CUST_CHURN_INFO_200710 aa on aa.cust_id=b.cust_id inner join CUST_CCP_INFO_200710 cc on cc.cust_id=b.cust_id left outer join V_PRE_EXP_GROUP_CUST1 a on b.cust_id = a.cust_id where (a.cust_id is not null or aa.on_net_time=4 or aa.LEAVE_DATE <= '3000-11-30 00:00:00') group by b.cust_group_id,aa.pre_m_arpu_d

GROUP_ID               PRE_M_ARPU_D         ADD_SUM_NOW_ARPU                  LOSS_SUM_NOW_ARPU
---------------------- -------------------- --------------------------------- ---------------------------------
                    1. A                                                34.70                             34.70
                    2. A                                                20.60                             20.60

  2 record(s) selected.

最终结果在GROUP_ID+PRE_M_ARPU_D上还是一样的，只是LZ的还是三行，我的是两行。

myfriend2010

恩,其他表的cust_id是唯一的

原帖由 askgyliu 于 2007-12-13 16:28 发表


那其他几个TABLES呢？CUST_ID是唯一的吗？

myfriend2010

不可意料不会吧!

看你的sql怎么写!我的业务逻辑就是要求FULL OUTER JOIN的

原帖由 askgyliu 于 2007-12-13 15:27 发表
这里只是证明了有三个TABLES在做FULL OUTER JOIN的话，最后结果是不可预测的。

但是DB2会顺着SEQUENCE来JOIN，而最终结果是可能很出人意料的。