请求MySQL查询优化：用单查询替代临时表实现非活跃企业逻辑

我明白你现在的需求——把依赖临时表的非活跃企业查询改成单条SQL对吧？刚好可以用子查询或者左关联的方式来实现，不用再折腾临时表了。

先理清楚原逻辑：你之前的临时表应该是先筛选出有活跃记录（Action=10，时间在2018区间内）的Agency ID，再从AgencyChoiceTb里排除这些ID得到非活跃企业。下面是两种直接实现这个逻辑的单条查询方案：

方案1：使用NOT IN子查询 #

这种方式逻辑最直观，直接排除掉有活跃记录的Agency ID：

SELECT ac.id
FROM AgencyChoiceTb ac
WHERE ac.id NOT IN (
    -- 子查询获取有活跃记录的Agency ID
    SELECT DISTINCT cd.Agency_choice_id
    FROM ClientDataTb cd
    JOIN HistoryCreditTb hc ON cd.Id = hc.Client_Id
    WHERE hc.`Action` = 10
      AND hc.Time BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59'
)

方案2：使用LEFT JOIN + IS NULL（推荐） #

这种方式在性能上通常比NOT IN更稳定，尤其是当子查询可能返回NULL值时，不会出现意料之外的结果：

SELECT ac.id
FROM AgencyChoiceTb ac
LEFT JOIN (
    -- 子查询获取有活跃记录的Agency ID集合
    SELECT DISTINCT cd.Agency_choice_id
    FROM ClientDataTb cd
    JOIN HistoryCreditTb hc ON cd.Id = hc.Client_Id
    WHERE hc.`Action` = 10
      AND hc.Time BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59'
) active_agencies ON ac.id = active_agencies.Agency_choice_id
-- 筛选出没有匹配到活跃记录的Agency，就是非活跃企业
WHERE active_agencies.Agency_choice_id IS NULL

额外优化建议 #

• 把原查询里的隐式JOIN改成了显式JOIN，可读性更强，也是SQL的最佳实践
• 子查询里不需要再关联AgencyChoiceTb，因为ClientDataTb已经存储了Agency_choice_id，直接取这个字段就能减少关联层级
• 确保以下字段有索引，能大幅提升查询速度：
  • HistoryCreditTb.Client_Id
  • ClientDataTb.Agency_choice_id
  • AgencyChoiceTb.id
  • 如果时间范围较大，给HistoryCreditTb.Time加索引也能加速过滤

内容的提问来源于stack exchange，提问作者Viktor Dreamwish