Db2查询中的联接
Db2查询中的联接
提问于 2012-11-06 14:15:40
我已经使用inner join连接了两个表。这里，表TPREG的主键是co_id和pol_id
SELECT (SELECT etbl_desc_txt
FROM uding604.tedit e
WHERE etbl_typ_id = 'PITCD'
AND co_id = 'CP'
AND etbl_valu_id = p.pol_ind_typ_cd) pol_ind_typ_cd,
(SELECT etbl_desc_txt
FROM uding604.tedit e
WHERE etbl_typ_id = 'PAYT'
AND co_id = 'CP'
AND etbl_valu_id = p.POL_BILL_TYP_CD) POL_BILL_TYP_CD,
POL_PAC_DRW_DY,
(SELECT etbl_desc_txt
FROM uding604.tedit e
WHERE etbl_typ_id = 'PAYM'
AND co_id = 'CP'
AND etbl_valu_id = p.POL_BILL_MODE_CD) POL_BILL_MODE_CD,
t.SOURCE_CD
FROM uding604.tpol p,
uding604.TPREG t
WHERE p.co_id = t.co_id
AND p.pol_id = t.pol_id
AND p.co_id = 'CP'
AND p.pol_id = '000000011'
AND t.co_id = 'CP'
AND t.pol_id = '000000011'如何让这个查询更简单？
请指点一下。
Stack Overflow用户
回答已采纳
发布于 2012-11-06 15:05:07
您可以将子查询转换为join：。
SELECT e1.etbl_desc_txt as pol_ind_typ_cd,
e2.etbl_desc_txt as POL_BILL_TYP_CD,
POL_PAC_DRW_DY,
e3etbl_desc_txt as POL_BILL_MODE_CD,
t.SOURCE_CD
FROM uding604.tpol p,
uding604.TPREG t,
uding604.tedit e1,
uding604.tedit e2,
uding604.tedit e3
WHERE p.co_id = t.co_id
AND p.pol_id = t.pol_id
AND p.co_id = 'CP'
AND p.pol_id = '000000011'
AND t.co_id = 'CP'
AND t.pol_id = '000000011'
AND e1.etbl_typ_id = 'PITCD'
AND e1.co_id = 'CP'
AND e1.etbl_valu_id = p.pol_ind_typ_cd
AND e2.etbl_typ_id = 'PAYT'
AND e2.co_id = 'CP'
AND e2.etbl_valu_id = p.POL_BILL_TYP_CD
AND e3.etbl_typ_id = 'PAYM'
AND e3.co_id = 'CP'
AND e3.etbl_valu_id = p.POL_BILL_MODE_CD对select子句的查询比较清晰，而对Where子句的查询比较模糊。
我们能做的就是继续ANSI JOIN语法：
SELECT e1.etbl_desc_txt as pol_ind_typ_cd,
e2.etbl_desc_txt as POL_BILL_TYP_CD,
POL_PAC_DRW_DY,
e3etbl_desc_txt as POL_BILL_MODE_CD,
t.SOURCE_CD
FROM uding604.tpol p
JOIN uding604.TPREG t on (p.co_id = t.co_id AND p.pol_id = t.pol_id)
JOIN uding604.tedit e1 on (e1.etbl_valu_id = p.pol_ind_typ_cd and e1.co_id = p.co_id)
JOIN uding604.tedit e2 on (e2.etbl_valu_id = p.POL_BILL_TYP_CD and e2.co_id = p.co_id)
JOIN uding604.tedit e3 on (e3.etbl_valu_id = p.POL_BILL_MODE_CD and e3.co_id = p.co_id)
WHERE p.co_id = 'CP'
AND p.pol_id = '000000011'
AND t.co_id = 'CP'
AND t.pol_id = '000000011'
AND e1.etbl_typ_id = 'PITCD'
AND e2.etbl_typ_id = 'PAYT'
AND e3.etbl_typ_id = 'PAYM'现在你想要什么更清楚了。
Note1。您可以尝试删除
AND t.co_id = 'CP'
AND t.pol_id = '000000011'因为逻辑不是必须的。
Note2。您必须测试这些查询是否返回与您的查询相同的结果。
Note3。你应该始终使用ANSI JOIN语法，当你不期望它的时候，它会帮助你。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13245424
复制相关文章
相似问题
腾讯云开发者
