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')  &gt;=  #{exeTime}
63         </if>
64         <if test=" doneTime !='' and doneTime != null">
65             and to_char(TPT.INSERT_TIME,'yyyy-MM-dd') &lt;=  #{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')  &gt;=  #{exeTime}
        </if>
        <if test=" doneTime !='' and doneTime != null">
            and to_char(TPT.INSERT_TIME,'yyyy-MM-dd') &lt;=  #{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
复制代码

 

posted on   ~码铃薯~  阅读(3876)  评论(0)  编辑  收藏  举报

编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY

导航

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示