SQL：如何比较不同行中的数据，只选择唯一的“对”，假设只有两个列？

I have several tables (bold means primary key):

我有几个表(粗体表示主键):

Dancer(dancer_name, gender, age)

舞者(dancer_name,性别,年龄)

Dance(dancer_name, dvd_id, song_title)

舞蹈(dancer_name,dvd_id,song_title)

Dvd(dvd_id, song_title, cost)

DVD(DVD_id,song_title,费用)

Song(dancer_name, song_title, genre)

歌曲(dancer_name,song_title,流派)

Launch(dancer_name, dvd_id, year)

启动(dancer_name,dvd_id,year)

I want to select the pairs of dancers whose song appear together in one or more dvds and for each pair to only print out once.

我想选择一对舞者,他们的歌曲在一个或多个dvds中一起出现,每对只能打印一次。

This is as close as I could get and it prints out the same pair twice, but their names in different columns:

这是我可以得到的尽可能接近它打印出相同的两次,但他们的名字在不同的列:

select distinct DANCER1.dancer_name, DANCER2.dancer_name, count(*) as count
  from Dancer DANCER1, Dancer DANCER2, Dance DANCE1, Dance DANCE2
 where DANCER1.dancer_name = DANCE1.dancer_name 
   and DANCER2.dancer_name = DANCE2.dancer_name 
   and DANCER1.dancer_name <> DANCER2.dancer_name
   and DANCE1.dvd_id = DANCE2.dvd_id
 group by DANCER1.dancer_name, DANCER2.dancer_name;

So instead of getting

所以而不是得到

Tom Jon
Jon Tom
Bob Sam
Sam Bob

I just want

我只是想

Tom Jon
Bob Sam

2 个解决方案

#1

If you change the test from DANCER1.dancer_name <> DANCER2.dancer_name to DANCER1.dancer_name < DANCER2.dancer_name, you should get the result you want.

如果将测试从DANCER1.dancer_name <> DANCER2.dancer_name更改为DANCER1.dancer_name ,则应获得所需的结果。

Hovever, as you are using the names as keys in the Dance table, you don't need to join the Dancer table, and the query may be simplified to this:

Hovever,因为你在Dance表中使用名称作为键,你不需要加入Dancer表,查询可以简化为:

SELECT DANCE1.dancer_name, DANCE2.dancer_name, count(*) as count
FROM Dance DANCE1
INNER JOIN Dance DANCE2
ON DANCE1.dvd_id = DANCE2.dvd_id
WHERE DANCE1.dancer_name < DANCE2.dancer_name
GROUP by DANCE1.dancer_name, DANCE2.dancer_name

#2

declare @tmpTable table
(
    ID BIGINT IDENTITY(1,1),
    User1 BIGINT,
    User2 BIGINT
)

declare @tmpParticipants table
(
    Participant1 BIGINT,
    Participant2 BIGINT
)

insert into @tmpTable
Select distinct SendByID, SendToID
from InternalMessaging

declare @cnt bigint, @i bigint = 1, @user1 bigint, @user2 bigint

select @cnt = count(*) from @tmpTable

While(@i <= @cnt)
begin
        select @user1 = User1, @user2 = User2 from @tmpTable where ID = @i

        if not exists(select 1 from @tmpParticipants where Participant1 = @user1 and Participant2 = @user2)
        if not exists(select 1 from @tmpParticipants where Participant1 = @user2 and Participant2 = @user1)
        begin
                insert into @tmpParticipants
                select @user1, @user2
        end

        set @i = @i + 1
end

select * from @tmpParticipants

It worked for me. I hope, it will help to resolve your problem.

它对我有用。我希望,这将有助于解决您的问题。

#1