SQL聚合查询新增列无需添加到GROUP BY：嵌套查询实现方案咨询

解决方案 #

你的写法存在两个问题：

1. IN子句的子查询只能返回单列（仅issue_id），你原有正常运行的查询返回了3列，直接放IN子句会触发语法错误
2. 现有写法仅能拿到额外属性字段，会丢失原查询计算出的prev_member_impact、curr_member_impact两个核心结果列

推荐使用CTE（公共表表达式）先拿到符合条件的issue_id和对应的两个指标，再关联原表取额外字段即可，代码如下：

WITH change_issues AS (
    SELECT rmd.issue_id,
        MAX(CASE WHEN as_of_date = '2021-08-08' THEN CURRENT.member_impact END) AS prev_member_impact,
        MAX(CASE WHEN as_of_date = '2021-08-15' THEN CURRENT.member_impact END) AS curr_member_impact
    FROM lod.ism_issue_summary_hist_wky CURRENT 
    INNER JOIN lod.rmd_iss_remed_summary rmd
        ON CURRENT.issue_id = rmd.issue_id
    WHERE as_of_date IN ('2021-08-08', '2021-08-15') 
    GROUP BY rmd.issue_id
    HAVING MAX(CASE WHEN as_of_date = '2021-08-08' THEN CURRENT.member_impact END) <> MAX(CASE WHEN as_of_date = '2021-08-15' THEN CURRENT.member_impact END )
)
SELECT 
    ci.issue_id,
    ci.prev_member_impact,
    ci.curr_member_impact,
    ish.ISSUE_TITLE,
    ish.ISM_STATUS,
    ish.ISSUE_OWNER_ORG,
    ish.ISSUE_OWNER,
    ish.ISSUE_APPROVER
FROM change_issues ci
INNER JOIN Lod.Ism_Issue_Summary_Hist_Wky ish
    ON ci.issue_id = ish.issue_id
-- 若同个issue_id存在多条历史记录，可加过滤条件取对应日期的属性避免重复，示例：
-- WHERE ish.as_of_date = '2021-08-15'

如果你的数据库不支持CTE语法，可以把子查询放到FROM子句中，效果完全一致：

SELECT 
    ci.issue_id,
    ci.prev_member_impact,
    ci.curr_member_impact,
    ish.ISSUE_TITLE,
    ish.ISM_STATUS,
    ish.ISSUE_OWNER_ORG,
    ish.ISSUE_OWNER,
    ish.ISSUE_APPROVER
FROM (
    SELECT rmd.issue_id,
        MAX(CASE WHEN as_of_date = '2021-08-08' THEN CURRENT.member_impact END) AS prev_member_impact,
        MAX(CASE WHEN as_of_date = '2021-08-15' THEN CURRENT.member_impact END) AS curr_member_impact
    FROM lod.ism_issue_summary_hist_wky CURRENT 
    INNER JOIN lod.rmd_iss_remed_summary rmd
        ON CURRENT.issue_id = rmd.issue_id
    WHERE as_of_date IN ('2021-08-08', '2021-08-15') 
    GROUP BY rmd.issue_id
    HAVING MAX(CASE WHEN as_of_date = '2021-08-08' THEN CURRENT.member_impact END) <> MAX(CASE WHEN as_of_date = '2021-08-15' THEN CURRENT.member_impact END )
) ci
INNER JOIN Lod.Ism_Issue_Summary_Hist_Wky ish
    ON ci.issue_id = ish.issue_id
-- 同样可加日期过滤避免重复行

注意事项 #

如果Lod.Ism_Issue_Summary_Hist_Wky表中同一个issue_id对应多条不同日期的记录，关联后会出现重复行，加对应日期的过滤条件取你需要的属性版本，即可得到和原查询一致的10条结果。

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