SQL Server查询：如何按规则关联Documents与Approvers表获取审批人

你现有SQL的核心问题有两个：

1. 使用OR拼接产品线和地点的匹配条件，导致只要满足其中一个维度条件就返回审批人，不符合需要同时匹配所有维度的规则
2. 没有做结果合并，会返回多行审批人记录，无法直接得到逗号分隔的名单

正确实现方案 #

方案1：关联两张表批量查询所有文档的审批人 #

SELECT 
  d.document_id, -- 请替换为你Documents表中实际的文档编号字段名
  GROUP_CONCAT(DISTINCT a.APPROVERS ORDER BY a.APPROVERS SEPARATOR ', ') AS approver_list
FROM 
  Documents d
INNER JOIN 
  Approvers a 
  ON d.Function = a.libraryname -- 匹配Function/Library维度
  AND d.Tier = a.level -- 匹配Tier/Level维度
  -- 产品线匹配规则
  AND (
    (d.`Product Line` = 'NOT PRODUCT LINE SPECIFIC' AND a.AnyProductLine = 'Yes')
    OR 
    (d.`Product Line` != 'NOT PRODUCT LINE SPECIFIC' AND (a.ProductLine = d.`Product Line` OR a.AnyProductLine = 'Yes'))
  )
  -- 地点匹配规则
  AND (
    (d.Locations = 'All Location' AND a.AnyLocation = 'Yes')
    OR 
    (d.Locations != 'All Location' AND (a.Location = d.Locations OR a.AnyLocation = 'Yes'))
  )
GROUP BY d.document_id

注：如果使用SQL Server请将GROUP_CONCAT替换为STRING_AGG，Oracle请替换为LISTAGG。

方案2：单独查询编号为5的文档的审批人 #

假设编号5的文档属性为Function=OPERATIONS、Tier=A、Product Line为指定值、Locations为指定值，查询语句如下：

SELECT 
  GROUP_CONCAT(DISTINCT APPROVERS ORDER BY APPROVERS SEPARATOR ', ') AS approver_list
FROM
  approvers
WHERE
  libraryname = 'OPERATIONS' 
  AND level = 'A' 
  AND (ProductLine = '替换为该文档实际的产品线' OR AnyProductLine = 'Yes')
  AND (Location = '替换为该文档实际的地点' OR AnyLocation = 'Yes')

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