php – MYSQL查询 – 获取与该帖子相关的最新评论

Illegal mix of collations (utf8_general_ci,IMPLICIT) and
(utf8_unicode_ci,IMPLICIT) for operation ‘=’

通常是由于您的列和表的定义.这通常意味着在等号的任一侧有不同的归类.您需要做的是选择一个,并在查询中包含该决定.

这里的排序规则是在@prev_value的CROSS JOIN中,需要使用明确的排序规则.

我还将“row_number”逻辑略微更改为单个交叉连接,并将if逻辑移动到选择列表的极限.

下面显示一些样品数据.需要样本数据来测试查询.任何试图通过实例解答您的问题的人都需要数据.我在这里包括的原因有两个方面.

所以你会明白我所呈现的任何结果
>以便将来当您提出另一个sql相关问题时,您将了解提供数据的重要性.这样做不仅对我们更方便.如果asker提供样本数据,那么asker将已经明白了 – 这不会是一些陌生人的发明,他们花了一些时间来帮助他们.

样品数据

请注意表中缺少一些列,仅包括表格详细信息中指定的列.

此示例数据针对单个帖子有5条评论(没有喜欢的记录)

CREATE TABLE Posts 
(
`id` int,`uuid` varchar(7) collate utf8_unicode_ci,`imageLink` varchar(9) collate utf8_unicode_ci,`date` datetime
 );

INSERT INTO Posts(`id`,`uuid`,`imageLink`,`date`)
VALUES
(145,'abcdefg','blah blah','2016-10-10 00:00:00') ;

CREATE TABLE   USERS
(
`id` int,`username` varchar(15) collate utf8_unicode_ci,`profileImage` varchar(12) collate utf8_unicode_ci,`date` datetime
) ;

INSERT INTO     USERS(`id`,`username`,`profileImage`,'used_by_already','blah de blah','2014-01-03 00:00:00') ;


CREATE TABLE Activity
(
`id` int,`uuid` varchar(4) collate utf8_unicode_ci,`uuidPost` varchar(7) collate utf8_unicode_ci,`type` varchar(40) collate utf8_unicode_ci,`commentText` varchar(11) collate utf8_unicode_ci,`date` datetime
) ;

INSERT INTO Activity (`id`,`uuidPost`,`type`,`commentText`,`date`)
 VALUES
(345,'a100','comment','lah lha ha','2016-07-05 00:00:00'),(456,'a101','lah lah lah','2016-07-06 00:00:00'),(567,'a102','lha lha ha','2016-07-07 00:00:00'),(678,'a103','ha lah lah','2016-07-08 00:00:00'),(789,'a104','hla lah lah','2016-07-09 00:00:00') ;

[sql标准行为：每个Post查询2行]

这是我的初步查询,并进行了一些更正.我更改了选择列表的列顺序,以便当我提供结果时,您会看到一些注释相关的数据.请研究他们提供的结果,以便您可以了解查询将会做什么.由于我已经注意到的原因,我正在处理的样本数据中不存在#前面的列.

SELECT
      Posts.id,rcom.uuidPost,rcom.commentText,rcom.`date` commentDate 
    #,Posts.caption
    #,Posts.`date`,USERS.username
    #,COALESCE(A.LikeCNT,0) num_likes
FROM Posts
INNER JOIN USERS ON Posts.id = 145
            AND USERS.id = 145
LEFT JOIN (
          SELECT
                COUNT(A.uuidPost) LikeCNT,A.UUIDPost
          FROM Activity A
          WHERE type = 'like'
          GROUP BY
                A.UUIDPOST
          ) A ON A.UUIDPost = Posts.uuid 
LEFT JOIN (
      SELECT
            @row_num := IF(@prev_value=UUIDPOST,commentText,uuidPost,`date`,@prev_value := UUIDPOST
      FROM Activity
      CROSS JOIN ( SELECT @row_num := 1,@prev_value := '' collate utf8_unicode_ci  ) xy
      WHERE type = 'comment'
      ORDER BY
            uuidPost,`date` DESC
      ) rcom ON rcom.uuidPost  = Posts.UUID
            AND rcom.row_number <= 2
ORDER BY
      posts.`date` DESC
      ;

See a working demonstration of this query at SQLFiddle

Results：

|  id |    uuid | uuidPost | commentText |                   date |                      date |  id |        username | profileImage | num_likes |
|-----|---------|----------|-------------|------------------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg |  abcdefg | hla lah lah | July,09 2016 00:00:00 | October,10 2016 00:00:00 | 145 | used_by_already | blah de blah |         0 |
| 145 | abcdefg |  abcdefg |  ha lah lah | July,08 2016 00:00:00 | October,10 2016 00:00:00 | 145 | used_by_already | blah de blah |         0 |

有2个ROWS – 如预期.最近一次评论的一行,以及下一个最近评论的另一行.这是sql的正常行为,直到在此答案下添加评论时,问题的读者会认为这种正常行为是可以接受的.

这个问题缺乏明确的“预期成果”.

[选项1：每个Post查询一行,最多2个评论,添加列]

在下面的评论中,显示您不需要每个帖子2行,这将是一个简单的修复.那么它很简单,但是有选项,选项由用户以需求的形式决定.如果这个问题有一个“预期的结果”,那么我们知道选择哪个选项.不过这里有一个选择

SELECT
      Posts.id,max(case when rcom.row_number = 1 then rcom.commentText end) Comment_one,max(case when rcom.row_number = 2 then rcom.commentText end) Comment_two
    #,`date` DESC
      ) rcom ON rcom.uuidPost  = Posts.UUID
            AND rcom.row_number <= 2
GROUP BY
      Posts.id,Posts.uuid
    #,0)
ORDER BY
      posts.`date` DESC
      ;

See the second query working at SQLFiddle

Results of query 2：

|  id |    uuid | Comment_one | Comment_two |                      date |  id |        username | profileImage | num_likes |
|-----|---------|-------------|-------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg | hla lah lah |  ha lah lah | October,10 2016 00:00:00 | 145 | used_by_already | blah de blah |         0 |

**选项2,将最近的评论连接成一个逗号分隔的列表**

SELECT
      Posts.id,group_concat(rcom.commentText) Comments_two_concatenated
    #,0)
ORDER BY
      posts.`date` DESC

See this third query working at SQLFiddle

Results of query 3：

|  id |    uuid | Comments_two_concatenated |                      date |  id |        username | profileImage | num_likes |
|-----|---------|---------------------------|---------------------------|-----|-----------------|--------------|-----------|
| 145 | abcdefg |    hla lah lah,ha lah lah | October,10 2016 00:00:00 | 145 | used_by_already | blah de blah |         0 |

**摘要**

我已经提交了3个查询,每个查询仅显示最近的2个注释,但每个查询都以不同的方式进行.第一个查询(默认行为)将为每个帖子显示2行.选项2添加一列,但删除第二行.选项3连接2个最近的评论.

请注意：

>问题缺少涵盖所有列的表定义
>该问题缺少任何样本数据,这使得您更难了解此处提供的任何结果,但也更难为我们准备解决方案
>这个问题还没有确定的“预期结果”(需要的输出),这导致了回答的进一步复杂化

我希望附加的提供的信息将会有一些用处,而且现在你也知道sql将数据呈现为多行是正常的.如果您不想要正常的行为,请具体说明您在问题中真正想要的内容.

后记.要包括另一个子查询“跟随”,您可以使用与您已有的子查询相似的子查询.它可以在该子查询之前或之后添加.您也可以在sqlfiddle here看到它的使用

LEFT JOIN (
          SELECT
                COUNT(*) FollowCNT,IdOtherUser
          FROM Activity
          WHERE type = 'Follow'
          GROUP BY
                IdOtherUser
          ) F ON USERS.id = F.IdOtherUser

当添加另一个子查询可能会解决您对更多信息的需求时,总体查询可能会随数据增长而变慢.一旦您确定了您真正需要的功能,可能会考虑在这些表上需要哪些索引. (我相信你会被建议单独要求这个建议,如果你确实包括1.你的表的完整DDL和2.查询的解释计划.)