mysql - 如何在 MySQL 中的特定条件下为对字符串生成唯一 ID

Question

我是 SQL 新手，请帮助我解决问题。

我有一个站点私有(private)消息表，是在引擎(phpbb 到 drupal)之间迁移后得到的

比方说，它有 3 列:

• mid - 消息 ID
• thread - 用于线程 id
• recipient - 用户 ID，谁将收到消息。

每条消息都显示在收件人和作者的邮箱中，因此每条消息我有 2 个字符串。

问题是消息的thread id不正确(基本上，它们等于mid)，所以2之间的对应关系用户显示为一百个单独的线程。

例如:

user 100 wrote message to user 101 
user 101 replied       to user 100
user 102 wrote message to user 100

表格看起来像:

 ________________________________
| mid    | thread    | recipient |
|        |           |           |
| 1      | 1         | 101       |  ← message 1 in recipient's mailbox    
| 1      | 1         | 100       |  ← message 1 in author's mailbox
| 2      | 2         | 100       |    ...
| 2      | 2         | 101       |
| 3      | 3         | 100       |
| 3      | 3         | 102       |
|________|___________|___________|

我的目标是为具有相同接收者对的每组线对提供相同的线程 ID:

 ________________________________
| mid    | thread    | recipient |
|        |           |           |
| 1      | 1         | 101       |  }
| 1      | 1         | 100       |  }    Correspondence between 100 and 101 must
| 2      | 1         | 100       |  }    have the same thread id (1)
| 2      | 1         | 101       |  }
| 3      | 2         | 100       |
| 3      | 2         | 102       |
|________|___________|___________|

更新:

对于同一收件人对之间的所有当前对话，线程 ID 必须相同。 (不用担心进一步的对话，用户可以选择在写消息时打开新线程，这将生成新的线程 ID，或者在现有线程中回复，这将使用现有线程 ID 标记消息。我只想收集线程中每 2 个用户之间的现有消息吨数)。

我想这就像某种循环，它将搜索所有收件人 ID 的每个中间值，并在升序排序后将它们合并到数组中:

• 对于中间 1:(100, 101)
• 对于中间 2:(100, 101)
• 对于中间 3:(100, 102)

然后给相同的数组相同的 id，这将是我想要的线程 id:

• (100, 101) = 1
• (100, 101) = 1
• (100, 102) = 2

不确定，我的算法是否可以只使用 SQL 查询

Answer 1

我将其放入 SQL Fiddle 中:http://sqlfiddle.com/#!2/83bdc/25

假设您的表名称是 messages 那么我会这样做:

Select messages.*, FirstThread 
From messages

Inner Join

(
-- Find a pair of dudes for each message and show the earliest thread of each
Select ThreadsForPairs.*, FirstThread From

(

Select mid
, Min(recipient) AS FirstDude
, Max(recipient) AS SecondDude
, thread

From messages

Group By mid, thread

) ThreadsForPairs

Left Join

(
-- Find the earliest thread for every pair of dudes
Select FirstDude, SecondDude, Min(thread) AS FirstThread

From

-- For every message, get the dudes involved
(
Select mid
, Min(recipient) AS FirstDude
, Max(recipient) AS SecondDude
, thread

From messages

Group By mid, thread
) PairsForMessages

Group By FirstDude, SecondDude

) FirstThreadForPairs

ON ThreadsForPairs.FirstDude = FirstThreadForPairs.FirstDude
AND ThreadsForPairs.SecondDude = FirstThreadForPairs.SecondDude

) FirstThreadForEveryMessage

On messages.mid = FirstThreadForEveryMessage.mid

正如您将看到的，我与您的输出不完全匹配，因为消息 ID 3 将具有线程 3 而不是线程 2，但我认为这是相同的想法...