sql多表连接查询查询

select count(*) from (
       SELECT trade_type, txn_tms, pay_type, txn_num, txn_amt, shift_id, DEVICE_NID, cur_station
        FROM AFC_CC_USER.DETAIL_NOD_TRANSACTION_RECORD_NORMAL
        WHERE SUBSTR(DEVICE_NID, 5, 2) = '03'
        UNION ALL
        SELECT trade_type, txn_tms, pay_type, txn_num, txn_amt, shift_id, DEVICE_NID, cur_station
        FROM AFC_CC_USER.DETAIL_NOD_TRANSACTION_RECORD_ADJUST
        WHERE SUBSTR(DEVICE_NID, 5, 2) = '03'
    )a 
    left join tbl_civil_service b on a.shift_id = b.shift_id AND TO_CHAR(a.TXN_TMS, 'YYYY-MM-DD') = TO_CHAR(b.CUR_TMS, 'YYYY-MM-DD')
    WHERE a.txn_tms >= '2023-07-26 00:00:00' AND a.txn_tms <= '2023-07-26 23:00:00'

这段代码有什么问题，大家帮忙看看解答一下，a表26号就101条数据，b表15条，但是left join之后就变几千条了，咋优化

这两张表是不是有相同的字段和数据，不建议使用union all，你可以把前两个表的复合结果放在cte里，并且对相同字段设置非聚簇索引，那么他就走走的是index search 而不是range search

看一下ID有没有重复的或者是join字段的类型是不是都一样

a表和b表是不是还有相同的字段

不知道你这个问题是否已经解决, 如果还没有解决的话:

帮你找了个相似的问题, 你可以看下: https://ask.csdn.net/questions/7601707
除此之外, 这篇博客: sql常见的七种join中的 第三种：查询全B，采用right join 部分也许能够解决你的问题。

以下回答由chatgpt基于相关博客总结生成:

这段代码的问题在于，使用了UNION ALL将两个表的数据进行拼接，然后再进行LEFT JOIN，导致结果集中出现了重复的数据，从而使结果变成了几千条数据。为了优化这个问题，可以使用子查询的方式，分别对两个表进行LEFT JOIN，然后再进行拼接和去重。以下是代码优化的解决方案：

SELECT COUNT(*) FROM (
   SELECT a.trade_type, a.txn_tms, a.pay_type, a.txn_num, a.txn_amt, a.shift_id, a.DEVICE_NID, a.cur_station
   FROM (
      SELECT trade_type, txn_tms, pay_type, txn_num, txn_amt, shift_id, DEVICE_NID, cur_station
      FROM AFC_CC_USER.DETAIL_NOD_TRANSACTION_RECORD_NORMAL
      WHERE SUBSTR(DEVICE_NID, 5, 2) = '03' AND txn_tms >= '2023-07-26 00:00:00' AND txn_tms <= '2023-07-26 23:00:00'
   ) a
   LEFT JOIN (
      SELECT shift_id, CUR_TMS
      FROM tbl_civil_service
      WHERE TO_CHAR(CUR_TMS, 'YYYY-MM-DD') = '2023-07-26'
   ) b
   ON a.shift_id = b.shift_id
   WHERE TO_CHAR(a.TXN_TMS, 'YYYY-MM-DD') = TO_CHAR(b.CUR_TMS, 'YYYY-MM-DD')
   UNION ALL
   SELECT a.trade_type, a.txn_tms, a.pay_type, a.txn_num, a.txn_amt, a.shift_id, a.DEVICE_NID, a.cur_station
   FROM (
      SELECT trade_type, txn_tms, pay_type, txn_num, txn_amt, shift_id, DEVICE_NID, cur_station
      FROM AFC_CC_USER.DETAIL_NOD_TRANSACTION_RECORD_ADJUST
      WHERE SUBSTR(DEVICE_NID, 5, 2) = '03' AND txn_tms >= '2023-07-26 00:00:00' AND txn_tms <= '2023-07-26 23:00:00'
   ) a
   LEFT JOIN (
      SELECT shift_id, CUR_TMS
      FROM tbl_civil_service
      WHERE TO_CHAR(CUR_TMS, 'YYYY-MM-DD') = '2023-07-26'
   ) b
   ON a.shift_id = b.shift_id
   WHERE TO_CHAR(a.TXN_TMS, 'YYYY-MM-DD') = TO_CHAR(b.CUR_TMS, 'YYYY-MM-DD')
) c

这个解决方案中，首先对两个表进行子查询，分别筛选出需要的数据，并且过滤掉不符合条件的数据。然后再通过LEFT JOIN将两个子查询结果连接在一起，得到最终的结果。最后再使用COUNT函数对结果集进行统计。通过这种方式，可以避免重复数据的出现，从而得到准确的结果。

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^