检查表中的多行，如果行匹配，则将它们计数为1

Question

我有一个名为private_messages的表，其结构如下：

id     message_from     message_to

我正在尝试确定活动聊天室的数量，但该表存储所有发送和接收的消息，因此可能有许多行Alice位于message_from和message_to中。

假设我的private_messages表有4行：

id   message_from    message_to
1    Alice           conor
2    Alice           conor
3    connor          Alice
4    Anderson        conor

艾丽斯和康纳之间的对话应该被认为是1。因此，当我回音时，比方说$active_conversations，应该打印数字2。

我假设这涉及到一个数组？即$conversations = [];.但我不知道或不明白在此之后该做些什么。

Answer 1

select count(id) from private_messages group by message_from,message_to

那么你就得到了适当的结果

Answer 2

如果你只想数一数对话，你可以用这样的方法

SELECT COUNT(DISTINCT
             CONCAT(
                    LEAST(message_from,message_to),
                    '->',
                    GREATEST(message_from,message_to)
                    )
             ) AS active_conversations
FROM private_messages;

木琴

产出：

active_conversations
2

如果你想了解对话的细节，你可以用这个

SELECT DISTINCT
       LEAST(message_from,message_to) as FirstPerson,
       GREATEST(message_from,message_to) as SecondPerson
FROM private_messages;

木琴

产出：

FirstPerson     SecondPerson
Alice           conor
Anderson        conor

Answer 3

其中一个问题是，private\_messages中的元组('connor'，'Alice')和('Alice'，'connor')代表着相同的谈话/房间，不管你在计算什么。

一种方法是得到不同值的总数，然后除以2，但这似乎并不是正确的方法。(我们可以设置一些边缘情况，在这种情况下，返回的结果不是整数，或者少于活动房间的实际数量.e.g

id   message_from    message_to
--   ------------    ----------
1    Alice           conor
2    Alice           conor
3    conor           Alice
4    Anderson        conor
5    Cooper          Alice
6    Dalton          Allman

另一种方法是重新排序元组中的值(交换message_from和message_to)，它们在对话的另一端匹配message_from和message_two。例如，我们可以比较message_from和message_to，并在message_to比message_from“低”时交换它们。

例如，使用表达式返回m_one和m_two列中显示的值。

id   message_from    message_to      m_one       t_two
--   ------------    ----------      -----       -----
1    Alice           conor           Alice       conor
2    Alice           conor           Alice       conor
3    conor           Alice           Alice       conor
4    Anderson        conor           Anderson    conor
5    Cooper          Alice           Alice       Cooper
6    Allman          Dalton          Allman      Dalton

然后我们可以计算(m_one，m_two)元组的不同值。

第三种方法是丢弃message_from值高于message_to值的任何元组，去掉会话/房间的一侧。然后对剩下的不同的元组进行计数。但同样，这可能会排除一些只在一个方向上传递信息的对话/房间。

id   message_from    message_to     m_one       t_two
--   ------------    ----------     -----       -----
1    Alice           conor          Alice       conor
2    Alice           conor          Alice       conor
3    conor           Alice          Alice       conor
4    Anderson        conor          Anderson    conor
5    Cooper          Alice          (NULL)      (NULL)
6    Allman          Dalton         Allman      Dalton

(我们将讨论message_from等于message_to的会话/房间的角落情况。)

我们编写的SQL实际上取决于我们想要实现的算法。

我们可以使用一个组来折叠“匹配”行，并得到一个计数。或者我们可以使用COUNT(DISTINCT foo)来获取不同值的计数。

如果我们选择一个需要“交换”值的算法，我们可以使用使用LEAST和GREATEST函数的表达式。或MySQL IF()函数，或CASE表达式。

如果我们想从计数中排除行，我们可以在WHERE子句中包含一个谓词。

所有这些都是可能的，实现指定结果的方法有多种。