postgresql数据库left join将主表中的数据查询出多条的解决办法
今天遇到了一个比较纳闷的bug
1 select 2 tbd.item_id as item_ID, 3 tbi.item_code||'-'||tbi.item_name as item_name, 4 tb.budget_code ||'-'|| tb.budget_name as budget_name, 5 tbd.org_code ||'-'|| tot.org_name as org_code, 6 ROUND(tbi.TOTAL_amount,2) AS TOTAL_amount, 7 tbi.item_type, 8 tbi.ITEM_DEFINE, 9 tbd.exe_type, 10 tbd.BUDGET_DETAIL_ID, 11 tbd.product_code, 12 case when DECLARE_CARD_NUM='' then 0 13 when DECLARE_CARD_NUM is null then 0 14 else round(to_number(DECLARE_CARD_NUM,'9999999999999999999'),0) end 15 AS DECLARE_CARD_NUM, 16 case when ACTUAL_CARD_NUM='' then 0 17 when ACTUAL_CARD_NUM is null then 0 18 else round(to_number(ACTUAL_CARD_NUM,'9999999999999999999'),0) end 19 AS ACTUAL_CARD_NUM, 20 case when DECLARE_CREDITS_NUM='' then 0 21 when DECLARE_CREDITS_NUM is null then 0 22 else round(to_number(DECLARE_CREDITS_NUM,'9999999999999999999'),0) end 23 AS DECLARE_CREDITS_NUM, 24 case when ACTUAL_CREDITS_NUM='' then 0 25 when ACTUAL_CREDITS_NUM is null then 0 26 else round(to_number(ACTUAL_CREDITS_NUM,'9999999999999999999'),0) end 27 AS ACTUAL_CREDITS_NUM, 28 ROUND(tbd.DECLARE_TOTAL_AMOUNT,2) as DECLARE_TOTAL_AMOUNT , 29 ROUND(tbd.ACTUAL_TOTAL_AMOUNT,2) as ACTUAL_TOTAL_AMOUNT , 30 to_char(tbd.item_startdate, 'yyyy-MM-dd') item_startdate, 31 to_char(tbd.item_enddate, 'yyyy-MM-dd') item_enddate, 32 to_char(tbd.INSERT_TIME, 'yyyy-mm-dd hh24:mi:ss') INSERT_TIME, 33 tbd.detail_code, 34 TPT.operate_type, 35 to_char(tbd.update_time,'yyyy-mm-dd hh24:mi:ss'), 36 tbd.update_oper, 37 tbd.actual_price, 38 TPT.ID, 39 to_char(TPT.INSERT_TIME, 'yyyy-mm-dd hh24:mi:ss') OPERATE_TIME, 40 TPT.INSERT_OPER 41 from T_BUDGET_DETAIL tbd join T_BUDGET_ITEM tbi on tbd.item_id = tbi.item_id 42 left join T_BUDGET tb on tb.budget_id = tbi.budget_id 43 LEFT JOIN T_PROJECT_TRACE TPT ON TPT.BUDGET_DETAIL_ID = tbd.BUDGET_DETAIL_ID 44 left join t_organization tot on tbd.org_code = tot.org_code 45 where 1=1 46 <if test="budgetCode !='' and budgetCode != null"> 47 and tb.budget_code =#{budgetCode} 48 </if> 49 <if test="itemCode !='' and itemCode !=null "> 50 and tbi.item_code =#{itemCode,jdbcType=VARCHAR} 51 </if> 52 <if test="orgCode !='' and orgCode !=null"> 53 and tbd.org_code like #{orgCode,jdbcType=VARCHAR} 54 </if> 55 <if test="exeType !='' and exeType != null "> 56 and tbd.exe_type =#{exeType,jdbcType=VARCHAR} 57 </if> 58 <if test=" operateType !='' and operateType != null "> 59 and TPT.operate_type =#{operateType,jdbcType=VARCHAR} 60 </if> 61 <if test="exeTime !='' and exeTime != null "> 62 and to_char(TPT.INSERT_TIME,'yyyy-MM-dd') >= #{exeTime} 63 </if> 64 <if test=" doneTime !='' and doneTime != null"> 65 and to_char(TPT.INSERT_TIME,'yyyy-MM-dd') <= #{doneTime} 66 </if> 67 and tbd.status = '02' 68 and TPT.status is null 69 70 order by OPERATE_TIME desc
这条sql在运行的时候，主表
T_BUDGET_DETAIL 和t_budget_item两张表中明明只有一条数据，但是查询出来的结果却有两条数据。
出现这个问题的原因：
t_organization表又将t_project_trace表当成了主表。
t_project_trace表中有两条记录，这样无形中将
T_BUDGET_DETAIL 和t_budget_item表中的一条数据进行了重复 显示。
解决办法：（控制t_project_trace表只显示一条数据）
select tbd.item_id as item_ID, tbi.item_code||'-'||tbi.item_name as item_name, tb.budget_code ||'-'|| tb.budget_name as budget_name, tbd.org_code ||'-'|| tot.org_name as org_code, ROUND(tbi.TOTAL_amount,2) AS TOTAL_amount, tbi.item_type, tbi.ITEM_DEFINE, tbd.exe_type, tbd.BUDGET_DETAIL_ID, tbd.product_code, case when DECLARE_CARD_NUM='' then 0 when DECLARE_CARD_NUM is null then 0 else round(to_number(DECLARE_CARD_NUM,'9999999999999999999'),0) end AS DECLARE_CARD_NUM, case when ACTUAL_CARD_NUM='' then 0 when ACTUAL_CARD_NUM is null then 0 else round(to_number(ACTUAL_CARD_NUM,'9999999999999999999'),0) end AS ACTUAL_CARD_NUM, case when DECLARE_CREDITS_NUM='' then 0 when DECLARE_CREDITS_NUM is null then 0 else round(to_number(DECLARE_CREDITS_NUM,'9999999999999999999'),0) end AS DECLARE_CREDITS_NUM, case when ACTUAL_CREDITS_NUM='' then 0 when ACTUAL_CREDITS_NUM is null then 0 else round(to_number(ACTUAL_CREDITS_NUM,'9999999999999999999'),0) end AS ACTUAL_CREDITS_NUM, ROUND(tbd.DECLARE_TOTAL_AMOUNT,2) as DECLARE_TOTAL_AMOUNT , ROUND(tbd.ACTUAL_TOTAL_AMOUNT,2) as ACTUAL_TOTAL_AMOUNT , to_char(tbd.item_startdate, 'yyyy-MM-dd') item_startdate, to_char(tbd.item_enddate, 'yyyy-MM-dd') item_enddate, to_char(tbd.INSERT_TIME, 'yyyy-mm-dd hh24:mi:ss') INSERT_TIME, tbd.detail_code, TPT.operate_type, to_char(tbd.update_time,'yyyy-mm-dd hh24:mi:ss'), tbd.update_oper, tbd.actual_price, TPT.ID, to_char(TPT.INSERT_TIME, 'yyyy-mm-dd hh24:mi:ss') OPERATE_TIME, TPT.INSERT_OPER from T_BUDGET_DETAIL tbd join T_BUDGET_ITEM tbi on tbd.item_id = tbi.item_id left join T_BUDGET tb on tb.budget_id = tbi.budget_id LEFT JOIN T_PROJECT_TRACE TPT ON TPT.BUDGET_DETAIL_ID = tbd.BUDGET_DETAIL_ID left join t_organization tot on tbd.org_code = tot.org_code where 1=1 <if test="budgetCode !='' and budgetCode != null"> and tb.budget_code =#{budgetCode} </if> <if test="itemCode !='' and itemCode !=null "> and tbi.item_code =#{itemCode,jdbcType=VARCHAR} </if> <if test="orgCode !='' and orgCode !=null"> and tbd.org_code like #{orgCode,jdbcType=VARCHAR} </if> <if test="exeType !='' and exeType != null "> and tbd.exe_type =#{exeType,jdbcType=VARCHAR} </if> <if test=" operateType !='' and operateType != null "> and TPT.operate_type =#{operateType,jdbcType=VARCHAR} </if> <if test="exeTime !='' and exeTime != null "> and to_char(TPT.INSERT_TIME,'yyyy-MM-dd') >= #{exeTime} </if> <if test=" doneTime !='' and doneTime != null"> and to_char(TPT.INSERT_TIME,'yyyy-MM-dd') <= #{doneTime} </if> and tbd.status = '02' and TPT.status is null and TPT.update_time=(select max(update_time) from t_project_trace where BUDGET_DETAIL_ID=TPT.BUDGET_DETAIL_ID ) order by OPERATE_TIME desc
