mysql - 如何进行特定的 SELECT 查询

Question

首先，我想为我的问题的模糊标题表示歉意。我很难解释这种情况，因为我不是 SQL 专家。

我需要编写查询方面的帮助。

我有一个名为 HELLO-MESSAGES 的表，其中包含多个列:ID、From、To>、正文、已发送(包含消息已发送的数据)。

我有这些行:

1. 1, 345xxxxxxx, 339xxxxxxx, “嘿，你好吗？”, 2016 年 12 月 1 日 17:24
2. 2, 339xxxxxxx, 345xxxxxxx,“好的，谢谢，你呢？”, 2016 年 12 月 1 日 17:25
3. 3、345xxxxxxx、340xxxxxxx、“快速...”、2016 年 12 月 1 日 18:24

我想做的是在表格中显示所有不同的聊天。如果我的电话号码是345xxxxxxx，我想显示一个具有以下布局的表格:

<table>
  <tr>
    <td><b>339xxxxxxx</b><br>Fine thanks, and you?</td>
  </tr>
  <tr>
    <td><b>340xxxxxxx</b><br>The quick...</td>
  </tr>
</table>

例如，就像 WhatsApp 或任何其他 IM 一样。我想显示正在与我聊天的人的号码，以及该聊天中发送的最后一条消息。我在编程查询方面的经历非常糟糕:这是我到目前为止所产生的:

SELECT * FROM 'HELLO-MESSAGES' WHERE 'From'='345xxxxxxx' OR 'To'='345xxxxxxx'

但它会返回每条消息，正如预期的那样!

请帮助不太喜欢 SQL 的初学者:)

编辑

SELECT *
            FROM (

                         SELECT MAX(d.id) AS max_id
                           FROM (

                                  SELECT df.id
                                    FROM ( SELECT xf.from
                                                , xf.to
                                                , MAX(xf.sent) AS max_date
                                             FROM `HELLO-MESSAGES` xf
                                            WHERE xf.from = '" . $_SESSION['ID'] . "'
                                            GROUP BY xf.from, xf.to
                                         ) mf
                                    JOIN `HELLO-MESSAGES` df
                                      ON df.from = mf.from
                                     AND df.to   = mf.to
                                     AND df.sent = mf.max_date

                                   UNION ALL

                                  SELECT dt.id
                                    FROM ( SELECT xt.from
                                                , xt.to
                                                , MAX(xt.sent) AS max_date
                                             FROM `HELLO-MESSAGES` xt
                                            WHERE xt.to = '" . $_SESSION['ID'] . "'
                                            GROUP BY xt.from, xt.to
                                         ) mt
                                    JOIN `HELLO-MESSAGES` dt
                                      ON dt.from = mt.from
                                     AND dt.to   = mt.to
                                     AND dt.sent = mt.max_date

                                ) q1
                           JOIN `HELLO-MESSAGES` d
                             ON d.id = q1.id
                          GROUP BY IF(d.from<d.to,d.from,d.to),IF(d.from<d.to,d.to,d.from)
                     ) q2
            JOIN `HELLO-MESSAGES` r
                ON r.id = q2.max_id
         ORDER BY r.sent DESC, r.id DESC

是吗，@spencer7593？谢谢!

Answer 1

正如我在评论中指出的，对于包含大量行的表，为了提高查询性能，我会考虑实现一个额外的“最新消息”表，与 HELLO-MESSAGES 一起维护。 table 。但我不会深入探讨这一点，因为这没有解决您所问的具体问题。 (但我怀疑您会问这样的问题:如何提高获取“最新消息”的查询的性能......除了确保适当的索引可用，并且该表增长到很大的大小之外。)

<小时/>

要获取从特定电话号码发送的每个“对话”中的最新消息，您可以使用如下查询:

SELECT df.id
     , df.from
     , df.to
     , df.sent
     , df.body
  FROM ( SELECT xf.from
              , xf.to
              , MAX(xf.sent) AS max_date
           FROM `HELLO-MESSAGES` xf
          WHERE xf.from = '345xxxxxxx'
          GROUP BY xf.from, xf.to
       ) mf
  JOIN `HELLO-MESSAGES` df
    ON df.from = mf.from
   AND df.to   = mf.to
   AND df.sent = mf.max_date

如果给定对话有多条消息具有相同的“已发送”最大值，则此查询将返回所有这些行。

相应的查询可用于获取特定电话号码收到的每个“对话”中的最新消息。

SELECT dt.id
     , dt.from
     , dt.to
     , dt.sent
     , dt.body
  FROM ( SELECT xt.from
              , xt.to
              , MAX(xt.sent) AS max_date
           FROM `HELLO-MESSAGES` xt
          WHERE xt.to = '345xxxxxxx'
          GROUP BY xt.to, xt.from
       ) mt
  JOIN `HELLO-MESSAGES` dt
    ON dt.from = mt.from
   AND dt.to   = mt.to
   AND dt.sent = mt.max_date

我们可以合并这两个查询的结果，并仅返回唯一的 id这些消息的值(value)。我们可以将这些集合与 UNION ALL 组合起来运算符(operator)。 (MySQL 优化器通常不能很好地处理“OR”谓词；这就是将查询分为两部分的原因。)

SELECT df.id
  FROM ( SELECT xf.from
              , xf.to
              , MAX(xf.sent) AS max_date
           FROM `HELLO-MESSAGES` xf
          WHERE xf.from = '345xxxxxxx'
          GROUP BY xf.from, xf.to
       ) mf
  JOIN `HELLO-MESSAGES` df
    ON df.from = mf.from
   AND df.to   = mf.to
   AND df.sent = mf.max_date

 UNION ALL

SELECT dt.id
  FROM ( SELECT xt.from
              , xt.to
              , MAX(xt.sent) AS max_date
           FROM `HELLO-MESSAGES` xt
          WHERE xt.to = '345xxxxxxx'
          GROUP BY xt.to, xt.from
       ) mt
  JOIN `HELLO-MESSAGES` dt
    ON dt.from = mt.from
   AND dt.to   = mt.to
   AND dt.sent = mt.max_date

此查询返回的集合将包括 id每个对话中最新“发送”和“接收”消息的值。

如果我们保证 id “稍后”消息的行值将大于 id具有“较早”消息的行的值，我们可以利用这一点来处理对话中多个“最新”消息的问题；具有相同值 Sent 的两行或多行.

我们可以使用它来将先前查询的结果缩减为每个对话中的最新消息。我们将前一个查询的结果重新表示为 q1在下一个查询中:

SELECT MAX(d.id) AS max_id
  FROM (
         subquery
       ) q1
  JOIN `HELLO-MESSAGES` d
    ON d.id = q1.id
 GROUP BY IF(d.from<d.to,d.from,d.to), IF(d.from<d.to,d.to,d.from) 

这就是 id我们想要返回的消息的值。我们将该结果表示为 q2在下一个查询中。

SELECT r.id
     , r.from
     , r.to
     , r.sent
     , r.body
  FROM (
       ) q2
  JOIN `HELLO-MESSAGES` r
    ON r.id = q2.max_id
 ORDER BY r.sent DESC, r.id DESC

<小时/>

将所有这些放在一起，我们得到一个非常难看的查询:

SELECT r.id
     , r.from
     , r.to
     , r.sent
     , r.body
  FROM (

         SELECT MAX(d.id) AS max_id
           FROM (

                  SELECT df.id
                    FROM ( SELECT xf.from
                                , xf.to
                                , MAX(xf.sent) AS max_date
                             FROM `HELLO-MESSAGES` xf
                            WHERE xf.from = '345xxxxxxx'
                            GROUP BY xf.from, xf.to
                         ) mf
                    JOIN `HELLO-MESSAGES` df
                      ON df.from = mf.from
                     AND df.to   = mf.to
                     AND df.sent = mf.max_date

                   UNION ALL

                  SELECT dt.id
                    FROM ( SELECT xt.from
                                , xt.to
                                , MAX(xt.sent) AS max_date
                             FROM `HELLO-MESSAGES` xt
                            WHERE xt.to = '345xxxxxxx'
                            GROUP BY xt.to, xt.from
                         ) mt
                    JOIN `HELLO-MESSAGES` dt
                      ON dt.from = mt.from
                     AND dt.to   = mt.to
                     AND dt.sent = mt.max_date

                ) q1
           JOIN `HELLO-MESSAGES` d
             ON d.id = q1.id
          GROUP BY IF(d.from<d.to,d.from,d.to),IF(d.from<d.to,d.to,d.from)

       ) q2
  JOIN `HELLO-MESSAGES` r
    ON r.id = q2.max_id
 ORDER BY r.sent DESC, r.id DESC

<小时/>

编辑:缺少不明确的列引用的限定符

编辑:修复 GROUP BY 中的表达式对于 q2。 (这些行需要按 from 和 to 的不同组合值进行折叠/分组。修复此问题的一种方法是将表达式添加到 GROUP BY 。该修复已应用对于上面的查询。

此答案说明了返回指定结果的一种方法。这不一定是最好的方法。使用内联 View (MySQL 中的派生表)可能会造成(有时是显着的)性能损失。