mysql - 排除 'mirrored' 行

Question

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

旧表是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;

Answer 1

您可以将用户的 id 与 friend 的 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 user1 和较高的 id user2，但是看到您从用户/ friend 模型更改为用户/用户，我认为哪个用户成为 user1 可能并不重要。