mysql - 如何在 MySQL 子查询中使用 where 子句？

Question

我使用以下 MySQL 查询来访问聊天日志中最新的 20 条消息，并颠倒顺序，以便最后一条消息打印在屏幕上:

SELECT * 
FROM 
    (
        SELECT
            messageID,
            posterID,
            messageTime,
            message
        FROM
            chat_messages
        /* Subquery is used to get the most recent 20 by messageTime */
        ORDER BY 
            messageTime DESC
        LIMIT 20
    ) subq
/* Reorder the result of the subquery to put them back into ascending order */
ORDER BY 
    messageTime ASC

效果很好。问题是我现在正在尝试将群组添加到聊天功能中。在此过程中，我在 chat_messages 表中添加了一个名为“group”的列。主聊天日志是组 0，因此我需要更改上述查询以仅访问主聊天日志中的消息。这就是我被困住的地方。看来 MySQL 不允许我在子查询中添加 where 子句。我尝试了以下方法，但没有成功:

SELECT * 
FROM 
    (
        SELECT
            messageID,
            posterID,
            messageTime,
            message
        FROM
            chat_messages
        WHERE
            group = '0'
        /* Subquery is used to get the most recent 20 by messageTime */
        ORDER BY 
            messageTime DESC
        LIMIT 20
    ) subq
/* Reorder the result of the subquery to put them back into ascending order */
ORDER BY 
    messageTime ASC

我收到此错误消息(第 58 行是查询后的下一行):

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in xxxxxxx on line 58

按照this thread上写的内容进行操作我尝试了以下方法，但也不起作用:

SELECT * 
FROM 
    (
        SELECT
                (
                    SELECT
                        messageID,
                        posterID,
                        messageTime,
                        message
                    FROM
                        chat_messages
                    WHERE
                        group = '0'
                )
        FROM
            chat_messages
        /* Subquery is used to get the most recent 20 by messageTime */
        ORDER BY 
            messageTime DESC
        LIMIT 20
    ) subq
/* Reorder the result of the subquery to put them back into ascending order */
ORDER BY 
    messageTime ASC

如何使查询仅访问来自组 0 的消息？我只是不明白它怎么不起作用？

谢谢，
乔

Answer 1

GROUP 是 reserved word对于 MySQL，请在其周围放置反引号 `。

SELECT * 
FROM 
(
     SELECT messageID,
          posterID,
          messageTime,
          message
     FROM chat_messages
     WHERE `group` = '0'
        /* Subquery is used to get the most recent 20 by messageTime */
     ORDER BY  messageTime DESC
     LIMIT 20
 ) subq
/* Reorder the result of the subquery to put them back into ascending order */
ORDER BY  messageTime ASC