此错误讯息
操作’=’的排序规则(utf8_general_ci,IMPLICIT)和(utf8_unicode_ci,IMPLICIT)的非法混合
通常是由于您定义了列和表。通常,这意味着等号的两边都有不同的排序规则。您需要做的是选择一个并将该决定包括在您的查询中。
这里的排序规则问题在@prev_value的CROSS JOIN中,需要使用显式的排序规则。
我也将“ row_number”逻辑略微更改为单个交叉联接,并将if逻辑移至选择列表的极端。
一些样本数据显示在下面。需要样本数据来测试查询。任何尝试通过工作示例回答您的问题的人都将需要数据。我将其包含在此处的原因是双重的。
请注意,表中缺少某些列,仅包括表详细信息中指定的列。
该样本数据对单个帖子有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`, `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_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`, `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') ;
这是我最初的查询,并进行了一些更正。我更改了选择列表的列顺序,以便在显示结果时可以轻松地看到一些与注释相关的数据。请研究它们提供的结果,以便您了解查询的作用。由于我已经指出的原因,在我正在使用的示例数据中不存在以#开头的列。
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_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,@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_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
;
:
| 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的正常行为,除非在此答案下添加注释,否则问题的读者会认为该正常行为是可以接受的。
这个问题缺乏明确表达的“预期结果”。
在下面的评论中,您发现您不希望每个帖子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_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,@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_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
GROUP 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
;
:
| 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 |
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_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,@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_unicode_ci ) xy
WHERE type = 'comment'
ORDER BY
uuidPost
, `date` DESC
) rcom ON rcom.uuidPost = Posts.UUID
AND rcom.row_number <= 2
GROUP 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
:
| 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将数据显示为多行是正常的。如果您不希望这种正常行为,请具体说明您在问题中真正想要的是什么。
后记。要包含另一个“遵循”子查询,可以使用与已有子查询类似的子查询。它可以在该子查询之前或之后添加。您还可以看到它在使用中@R_404_1062@这里
LEFT JOIN (
SELECT
COUNT(*) FollowCNT
, IdOtherUser
FROM Activity
WHERE type = 'Follow'
GROUP BY
IdOtherUser
) F ON USERS.id = F.IdOtherUser
虽然添加另一个子查询可能会解决您对更多信息的需求,但总体查询可能会随着数据增长而变慢。确定了您真正需要的功能后,可能值得考虑在这些表上需要哪些索引。(我相信建议您单独咨询该建议,并且如果确实要确保包含1.表的完整DDL和2.查询的解释计划。)