sql server select query one column is the same and another is different

I want to be able to find occurrences in a table where one column is the same but another is different.

我希望能够在表中找到一列相同而另一列不同的事件。

Example table:

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

I want a query that will find all ids where team is different yet week is still the same so something like team does not equal team, but week does equal week.

我想要一个查询,找到团队不同的所有ID,但周仍然是相同的,所以像团队这样的东西不等于团队,但是周等于一周。

Basically I would like to know if any id changed teams in the same week, how do i do this?

基本上我想知道在同一周内是否有任何ID改变了球队,我该怎么做?

3 个解决方案

#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

#2

Something like this?

像这样的东西?

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

#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