SQL 服务器选择查询 一列相同，另一列不同

Question

我希望能够在表中查找一列相同但另一列不同的匹配项。

示例表：

id    team    week
1     1       1
1     2       1
2     2       1
2     1       2

我想要一个查询，该查询将找到团队不同但周仍然相同的所有 id，因此团队不等于团队，但周等于周。

基本上我想知道是否有任何ID在同一周内更换了团队，我该怎么做？

Answer 1

SELECT 
    t1.id, 
    t1.week
FROM 
    YourTable t1
    JOIN YourTable t2 
        ON t1.ID = t2.ID
        AND t1.team < t2.team
        AND t1.week = t2.week

Answer 2

像这样的东西？

SELECT distinct id
FROM TeamWeek
GROUP BY id, week
HAVING Count(team) > 1

Answer 3

SELECT ID
, COUNT(DISTINCT TEAM) AS CNT_TEAM
, COUNT(DISTINCT WEEK) AS CNT_WEEK
FROM TABLENAME
GROUP BY ID
HAVING COUNT(DISTINCT TEAM) > 1
AND COUNT(DISTINCT WEEK) = 1