此错误讯息
*** 作’=’的排序规则(utf8_general_ci,IMPLICIT)和(utf8_unipre_ci,IMPLICIT)的非法混合
通常是由于您定义了列和表。通常,这意味着等号的两边都有不同的排序规则。您需要做的是选择一个并将该决定包括在您的查询中。
这里的排序规则问题在@prev_value的CROSS JOIN中,需要使用显式的排序规则。
我也将“ row_number”逻辑略微更改为单个交叉联接,并将if逻辑移至选择列表的极端。
一些样本数据显示在下面。需要样本数据来测试查询。任何尝试通过工作示例回答您的问题的人都将需要数据。我将其包含在此处的原因是双重的。
- 以便您了解我提出的任何结果
- 这样,将来当您提出另一个与SQL有关的问题时,您就会了解提供数据的重要性。您这样做不仅对我们更方便。如果问询者提供了样本数据,那么问询者将已经理解了它-这不是某个陌生人的发明,他们花费了一些时间来提供帮助。
样本数据
请注意,表中缺少某些列,仅包括表详细信息中指定的列。
该样本数据对单个帖子有5条评论(没有记录喜欢的记录)
CREATE TABLE Posts (`id` int, `uuid` varchar(7) collate utf8_unipre_ci,`imagelink` varchar(9) collate utf8_unipre_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_unipre_ci, `profileImage` varchar(12) collate utf8_unipre_ci, `date` datetime) ;INSERT INTO USERS(`id`, `username`, `profileImage`, `date`)VALUES(145, 'used_by_already', 'blah de blah', '2014-01-03 00:00:00') ;CREATE TABLE Activity(`id` int, `uuid` varchar(4) collate utf8_unipre_ci, `uuidPost` varchar(7) collate utf8_unipre_ci, `type` varchar(40) collate utf8_unipre_ci, `commentText` varchar(11) collate utf8_unipre_ci, `date` datetime) ;INSERT INTO Activity (`id`, `uuid`, `uuidPost`, `type`, `commentText`, `date`) VALUES(345, 'a100', 'abcdefg', 'comment', 'lah lha ha', '2016-07-05 00:00:00'),(456, 'a101', 'abcdefg', 'comment', 'lah lah lah', '2016-07-06 00:00:00'),(567, 'a102', 'abcdefg', 'comment', 'lha lha ha', '2016-07-07 00:00:00'),(678, 'a103', 'abcdefg', 'comment', 'ha lah lah', '2016-07-08 00:00:00'),(789, 'a104', 'abcdefg', 'comment', 'hla lah lah', '2016-07-09 00:00:00') ;
[SQL标准行为:每个Post查询2行]
这是我最初的查询,并进行了一些更正。我更改了选择列表的列顺序,以便在显示结果时可以轻松地看到一些与注释相关的数据。请研究它们提供的结果,以便您了解查询的作用。由于我已经指出的原因,在我正在使用的示例数据中不存在以#开头的列。
SELECt Posts.id , Posts.uuid , rcom.uuidPost , rcom.commentText , rcom.`date` commentDate #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) num_likesFROM PostsINNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145LEFT 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,@row_num+1,1) as row_number , commentText , uuidPost , `date` , @prev_value := UUIDPOST FROM Activity CROSS JOIN ( SELECt @row_num := 1, @prev_value := '' collate utf8_unipre_ci ) xy WHERe type = 'comment' ORDER BY uuidPost , `date` DESC ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2ORDER BY posts.`date` DESC ;
在SQLFiddle上查看此查询的工作演示
结果 :
| 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行-如预期。一行用于最近的注释,另一行用于下一个最近的注释。这是SQL的正常行为,除非在此答案下添加注释,否则问题的读者会认为该正常行为是可以接受的。
这个问题缺乏明确表达的“预期结果”。
[选项1:每个邮政查询一行,最多2条注释,并增加了列数]
在下面的评论中,您发现您不希望每个帖子2行,这很容易解决。嗯,这很容易,但是有很多选择,而这些选择是由用户以需求的形式来决定的。如果问题有“预期结果”,那么我们将知道选择哪个选项。尽管如此,这是一种选择
SELECT Posts.id , Posts.uuid , 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 #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) num_likesFROM PostsINNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145LEFT 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,@row_num+1,1) as row_number , commentText , uuidPost , `date` , @prev_value := UUIDPOST FROM Activity CROSS JOIN ( SELECt @row_num := 1, @prev_value := '' collate utf8_unipre_ci ) xy WHERe type = 'comment' ORDER BY uuidPost , `date` DESC ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2GROUP BY Posts.id , Posts.uuid #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0)ORDER BY posts.`date` DESC ;
请参阅在SQLFiddle中运行的第二个查询
查询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 , Posts.uuid , group_concat(rcom.commentText) Comments_two_concatenated #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) num_likesFROM PostsINNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145LEFT 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,@row_num+1,1) as row_number , commentText , uuidPost , `date` , @prev_value := UUIDPOST FROM Activity CROSS JOIN ( SELECt @row_num := 1, @prev_value := '' collate utf8_unipre_ci ) xy WHERe type = 'comment' ORDER BY uuidPost , `date` DESC ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2GROUP BY Posts.id , Posts.uuid #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0)ORDER BY posts.`date` DESC
请参阅在SQLFiddle中运行的第三个查询
查询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这里
LEFT JOIN ( SELECT COUNT(*) FollowCNT , IdOtherUser FROM Activity WHERe type = 'Follow' GROUP BY IdOtherUser ) F ON USERS.id = F.IdOtherUser
虽然添加另一个子查询可能会解决您对更多信息的需求,但总体查询可能会随着数据增长而变慢。确定了您真正需要的功能后,可能值得考虑在这些表上需要哪些索引。(我相信建议您单独咨询该建议,并且如果确实要确保包含1.表的完整DDL和2.查询的解释计划。)
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)