SQL技术问询：如何基于聚合值过滤表并显示未参与聚合的列？

我有一个仅包含Composer_Tune、_Year、_Rank三列的SQL表，需求是用SQL找出每年列表中重复的Composer_Tune值，以及这些重复项对应的_Rank。

按Composer_Tune和_Year分组查询时，无法在结果中列出_Rank列；尝试用以下CTE代码时，触发报错：

"_Rank is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

错误代码：

WITH DUPE_DB AS (SELECT * FROM DB.dbo.[NAME] GROUP BY Composer_Tune, _Year HAVING COUNT(*)>1)
SELECT Composer_Tune, _Year, _Rank    
FROM DUPE_DB

已参考相关技术帖子但问题未解决，当前结果与期望结果的对比见下图：

方案一：使用窗口函数（推荐） #

通过窗口函数COUNT() OVER()按Composer_Tune和_Year分组统计记录数，筛选出重复的条目，同时保留_Rank列：

WITH DUPE_DB AS (
    SELECT 
        Composer_Tune,
        _Year,
        _Rank,
        -- 按曲目+年份分组统计数量
        COUNT(*) OVER (PARTITION BY Composer_Tune, _Year) AS tune_count
    FROM DB.dbo.[NAME]
)
SELECT Composer_Tune, _Year, _Rank
FROM DUPE_DB
WHERE tune_count > 1
-- 可选：按年份、曲目、排名排序，让结果更规整
ORDER BY _Year, Composer_Tune, _Rank;

方案二：关联子查询 #

先通过子查询找出存在重复的“曲目+年份”组合，再关联原表取出对应记录：

SELECT t1.Composer_Tune, t1._Year, t1._Rank
FROM DB.dbo.[NAME] t1
-- 子查询筛选出重复的曲目+年份组合
JOIN (
    SELECT Composer_Tune, _Year
    FROM DB.dbo.[NAME]
    GROUP BY Composer_Tune, _Year
    HAVING COUNT(*) > 1
) t2 
ON t1.Composer_Tune = t2.Composer_Tune AND t1._Year = t2._Year
ORDER BY t1._Year, t1.Composer_Tune, t1._Rank;

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