mysql - 排除“镜像”行

我目前正在尝试将旧的肮脏的“友谊”表迁移到新的表，但是我的数据转换存在问题。

旧表是hotlist：

id int(11) AI PK
id_user int(11)
id_friend int(11)
accepted enum('0','1','2','3','9')
date_sent timestamp
date_accepted timestamp
rejected tinyint(1)

我要迁移到的新版本是friends：

user1 int(11)
user2 int(11)
status enum('0','1','2')
date timestamp

新表的status列（'0'，'1'，'2'）分别表示请求是“待处理”，“已接受”或“已拒绝”。

因此，我从“待处理”请求的迁移开始：

INSERT INTO friends(user1, user2, status, date)
    SELECT id_user, id_friend, '0', date_sent
    FROM hotlist
    WHERE accepted='0' AND rejected=0;

好的，这很明显。

接下来，“拒绝”请求。当我需要计算它们时，我只是以相同的方式将它们全部转移：

INSERT INTO friends(user1, user2, status, date)
    SELECT id_user, id_friend, '2', date_sent
    FROM hotlist
    WHERE accepted='0' AND rejected=1

一切也都很好。

现在，我的困难在于“已接受”的请求。旧表中的样本数据（仅相关部分）：

| id_user  |  id_friend |  accepted  | ...
|    1     |     2      |      1     | ...
|    2     |     1      |      1     | ...
|    1     |     3      |      1     | ...
|    3     |     1      |      1     | ...

如您所见，每个友谊行都有一个针对两个用户的“镜像”行。我不希望新表中有此“双精度”数据。我看不到在“请求接受/拒绝”友谊系统中执行此操作的目的，如果我错了，请纠正我。

所以我想从旧桌子上转移的只是每个友谊两行中的一行。那就是它的样子：

| id_user  |  id_friend |  accepted  | ...
|    1     |     2      |      1     | ...
|    1     |     3      |      1     | ...

最后一件事。当用户接受请求时，镜像行是“人为地”创建的，因此具有date_accepted=date_sent。我已经告诉自己-“嘿，发送“真实”请求的同时接受是不可能的”-事实并非如此，有一打，所以以下查询给出了我想要的信息，除了立即接受的那些。

SELECT id_user, id_friend, date_accepted
    FROM hotlist
    WHERE accepted='1' AND date_sent!=date_accepted;

最佳答案

您可以将用户的ID与朋友的ID进行比较，并切换它们，以使最低的ID始终在第一位，然后使用group by消除重复。假设您想要最早的日期，可以将min（）应用于日期列。

这应该工作：

insert into friends (user1, user2, status, date)

select
    case when id_user < id_friend then id_user else id_friend end id_user,
    case when id_user > id_friend then id_user else id_friend end id_friend,
    accepted,
    min(date_accepted) date_accepted
from hotlist
where accepted = '1'
group by
    case when id_user < id_friend then id_user else id_friend end,
    case when id_user > id_friend then id_user else id_friend end,
    accepted

这将使具有较低ID用户1的用户和具有较高ID用户2的用户，但是看到您从用户/朋友模型更改为用户/用户，我认为哪个用户成为user1并不重要。