如何高效实现PAY表PAYID按CATEGORY_ID匹配规则取值

问题背景 #

现有以下数据表，需根据CATEGORY_ID的匹配规则从PAY表中获取PAYID，规则如下：

• 若PAYID已关联CATEGORY_ID，直接返回该PAYID；
• 若PAYID未关联CATEGORY_ID，则通过关联NETWORK_CATEGORY表和CATEGORIES表，依据BILL_ID匹配取值；
• 若PAYID未关联CATEGORY_ID且BILL_ID也未关联NETWORK_CATEGORY_ID，则将该PAY对应的CATEGORY_ID设为3（Uncategorized）。

数据表结构及数据 #

PAY表

BILL表

NETWORK_CATEGORY表

CATEGORIES表

原SQL使用多次UNION实现，效率较低，期望优化后满足：

• 传入categoryIds = 3时返回PAYID 102；
• 传入categoryIds = (1,2,3)时返回101、102、103。

高效SQL实现方案 #

核心思路是通过左关联+COALESCE一次性计算出每条PAY记录最终匹配的CATEGORY_ID，再根据传入的categoryIds过滤，避免多次UNION带来的性能损耗。

SELECT p.PAYID
FROM PAY p
LEFT JOIN BILL b ON p.BILL_ID = b.BILL_ID
LEFT JOIN NETWORK_CATEGORY nc ON b.NETWORK_CATEGORY_ID = nc.NETWORK_CATEGORY_ID
-- 按规则优先级计算最终的CATEGORY_ID
WHERE COALESCE(p.CATEGORY_ID, nc.CATEGORY_ID, 3) IN (:categoryIds)

逻辑解释 #

1. 左关联保留全量PAY记录：使用LEFT JOIN关联BILL和NETWORK_CATEGORY，确保即使没有匹配的BILL或NETWORK_CATEGORY，PAY记录也能被纳入计算；
2. COALESCE按规则取值：严格遵循业务规则的优先级：
   • 优先取PAY表自身非空的CATEGORY_ID；
   • 若PAY的CATEGORY_ID为空，则取通过BILL关联到的NETWORK_CATEGORY的CATEGORY_ID；
   • 若以上都为空，则默认设为3（Uncategorized）；
3. 一次性过滤结果：直接用计算出的最终分类ID匹配传入的categoryIds，无需多次拼接结果集。

结果验证 #

• 当categoryIds = 3时：
  PAYID 101最终分类为1、PAYID 103最终分类为2，均不匹配；仅PAYID 102最终分类为3，返回结果：102。
• 当categoryIds = (1,2,3)时：
  三条PAY记录的最终分类分别为1、3、2，均在传入集合中，返回结果：101, 102, 103。

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