如何改写查询以获取指定SSISDB包/项目的正确reference_id？

当前查询因PJ.project_id = ER.project_id的JOIN条件过滤掉部分数据，同时原查询的关联逻辑混淆了项目文件夹与环境文件夹的关系，导致部分包无法匹配到对应的环境引用ID。以下是改写后的查询，能正确获取每个包/项目对应的reference_id：

SELECT  
    PackagePathName = FORMATMESSAGE('\SSISDB\%s\%s\%s\', F_Prj.name, PJ.name, PK.name),
    EnvironmentPathName = FORMATMESSAGE('\SSISDB\%s\%s', F_Env.name, E.name),
    EnvironmentReferenceID = ER.reference_id,
    ProjectFolder = F_Prj.name,
    Project = PJ.name,
    Package = PK.name,
    EnvironmentFolder = F_Env.name,
    Environment = E.name,
    -- 测试用字段
    ER.reference_type,
    PJ.project_id AS pj_project_id,
    ER.project_id AS er_project_id
FROM 
    SSISDB.catalog.packages AS PK
INNER JOIN 
    SSISDB.catalog.projects AS PJ ON PK.project_id = PJ.project_id
INNER JOIN 
    SSISDB.catalog.folders AS F_Prj ON PJ.folder_id = F_Prj.folder_id
INNER JOIN 
    SSISDB.catalog.environment_references AS ER ON PJ.project_id = ER.project_id
LEFT JOIN 
    SSISDB.catalog.environments AS E 
        ON (ER.reference_type = 'A' AND E.name = ER.environment_name)
        OR (ER.reference_type = 'R' AND E.name = ER.environment_name AND E.folder_id = PJ.folder_id)
LEFT JOIN 
    SSISDB.catalog.folders AS F_Env ON E.folder_id = F_Env.folder_id

关键修改说明： #

• 调整关联顺序：从包和项目开始关联，确保所有包都能被纳入结果，再关联项目对应的环境引用（ER.project_id = PJ.project_id），这是环境引用与项目的直接关联，不会过滤掉无环境引用的项目（若需仅保留有环境引用的记录，可把LEFT JOIN改回INNER JOIN）。
• 区分项目文件夹与环境文件夹：用F_Prj关联项目所在文件夹，F_Env关联环境所在文件夹，避免原查询中混淆两者的错误。
• 修正环境关联逻辑：
  • 绝对引用（reference_type='A'）：通过environment_folder_name和environment_name匹配跨文件夹的环境。
  • 相对引用（reference_type='R'）：匹配同一项目文件夹下同名的环境。

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