SQL Server分组依据问题

Question

我有下表：

CREATE TABLE #weeks(
  ProposalLineWeekID INT, 
  StartWeek DATETIME, 
  EndWeek DATETIME, 
  Selected BIT, 
  WeekNumber INT
)

如何获取具有重复WeekNumber条目的所有ProposalLineWeekID值？例如

ProposalLineWeekID, WeekNumber 
------------------------------
2                   22
3                   6
4                   22

我想要附带WeekNumber的ProposalLineWeekID 2和4。

Answer 1

试试这个：

SELECT ProposalLineWeekID, WeekNumber
FROM #Weeks
WHERE WeekNumber IN
( 
    SELECT WeekNumber
    FROM #weeks
    GROUP BY WeekNumber
    HAVING COUNT(*) > 1
)

子查询查找所有重复的周数，外部查询获取相应的行。

Answer 2

如果我没理解错的话，我想这就是你想要的：

SELECT DISTINCT w1.ProposalLineWeekID, w1.WeekNumber
FROM weeks w1
INNER JOIN weeks w2 ON w2.WeekNumber = w1.WeekNumber 
  AND w2.ProposalLineWeekID <> w1.ProposalLineWeekID