SQL 实现总成员数超10的供应商全关联行筛选方案
核心问题根因
原有过滤逻辑失效的本质是过滤维度错配:
- 直接在最终查询层加
MEMBERS >=10条件时,数据库会逐行判断每行的MEMBERS值:既会把“总成员达标但单条明细MEMBERS小于10”的行误删除,也会错误保留“总成员不达标但单条明细MEMBERS大于10”的行,完全无法实现“按供应商总维度筛选、联动拉取对应全量关联行”的目标。
修正实现方案
整体调整逻辑为先圈定总成员数>10的供应商范围,再拉取该范围下所有明细行、对应汇总行,具体实现步骤:
- 新增合格供应商CTE:按供应商维度分组计算总成员数,用
HAVING子句直接筛出总成员超10的供应商集合,把范围筛选提前到分组阶段完成 - 基于合格集合关联原始FINAL表,仅提取达标供应商对应的全部原始明细行,作为UNION的第一部分
- 直接复用合格集合的计算结果生成对应供应商的汇总聚合行,作为UNION的第二部分,避免重复计算
- 合并两部分结果后,沿用原代码的排序逻辑输出,保持原有展示效果不变
完整可运行代码
WITH QUALIFIED_VENDOR AS ( SELECT VENDOR_ID, VEND_NAME, SUM(MEMBERS) AS TOTAL_MEMBERS FROM FINAL GROUP BY VENDOR_ID, VEND_NAME HAVING SUM(MEMBERS) > 10 -- 按供应商总成员数做范围过滤 ), TT2 AS ( -- 达标供应商的全量原始明细行 SELECT VENDOR_ID, VEND_NAME, VEND_ADRLN1, VEND_ADRLN2, VEND_CITY, VEND_STATE, VEND_ZIP, COUNTY_NAME, CONTRACT, MEMBERS FROM FINAL f INNER JOIN QUALIFIED_VENDOR q ON f.VENDOR_ID = q.VENDOR_ID AND f.VEND_NAME = q.VEND_NAME UNION ALL -- 达标供应商对应的总成员汇总行 SELECT VENDOR_ID, VEND_NAME, NULL AS VEND_ADRLN1, NULL AS VEND_ADRLN2, NULL AS VEND_CITY, NULL AS VEND_STATE, NULL AS VEND_ZIP, NULL AS COUNTY_NAME, NULL AS CONTRACT, TOTAL_MEMBERS AS MEMBERS FROM QUALIFIED_VENDOR q ), SH4 AS ( SELECT * FROM TT2 ) SELECT * FROM SH4 ORDER BY 2, CASE WHEN VEND_ADRLN1 IS NULL THEN 'ZZ' ELSE VEND_NAME END, 8
优化提示:如果业务场景中
VENDOR_ID是供应商唯一标识,可将关联条件简化为仅用VENDOR_ID匹配,避免因供应商名称重复、名称变更导致的匹配错误。
内容的提问来源于stack exchange,提问作者sm86
相关产品推荐
相关产品推荐

