mysql - 为什么 LIMIT 运算符不能正常工作？

Question

我编写了下一个查询 SQL 来获取最后的评论和重新评论:

SELECT c.*, ar.ArticleName, ar.idArticle, du.DetailToUsersName, du.DetailToUsersPhoto, COUNT(c.idCommentToArticle) AS CNT, CASE WHEN d.Count IS NULL THEN 0 ELSE d.Count END AS CountLikes 
FROM commenttoarticle c 

INNER JOIN (SELECT CommentToArticlePID FROM commenttoarticle 
GROUP BY CommentToArticlePID 
ORDER BY CommentToArticlePID LIMIT 3) AS articleComments 
USING (CommentToArticlePID) 

LEFT JOIN article ar ON c.CommentToArticleIdArticle = ar.idArticle 
LEFT JOIN detailtousers du ON du.idDetailToUsers = c.CommentToArticleIdUser 
LEFT JOIN `likes` d ON (d.IdNote = c.idCommentToArticle AND d.LikeType = 6) 

WHERE c.CommentToArticleIdArticle = 11 
GROUP BY c.idCommentToArticle

那么，为什么子查询 select 中的运算符 LIMIT 3 不起作用呢？现在，此查询显示表 commenttoarticle

中的所有行

我似乎需要做一些类似的事情:

SELECT...
FROM (select * from commenttoarticle commenttoarticle c INNER JOIN
            (SELECT distinct(CommentToArticlePID)
              FROM commenttoarticle b
                ORDER BY CommentToArticlePID
                   LIMIT  2) AS commenttoarticle USING (CommentToArticlePID)) as c

          LEFT JOIN article ar ON c.CommentToArticleIdArticle = ar.idArticle...

将表评论转储到文章:

CREATE TABLE IF NOT EXISTS `commenttoarticle` (
  `idCommentToArticle` int(11) NOT NULL AUTO_INCREMENT,
  `CommentToArticleTime` int(11) NOT NULL,
  `CommentToArticleIdArticle` int(11) NOT NULL,
  `CommentToArticleComment` text NOT NULL,
  `CommentToArticleIdUser` int(11) NOT NULL,
  `CommentToArticlePID` int(11) NOT NULL,
  PRIMARY KEY (`idCommentToArticle`),
  UNIQUE KEY `idCommentToArticle_UNIQUE` (`idCommentToArticle`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=59 ;


INSERT INTO `commenttoarticle` (`idCommentToArticle`, `CommentToArticleTime`, `CommentToArticleIdArticle`, `CommentToArticleComment`, `CommentToArticleIdUser`, `CommentToArticlePID`) VALUES
(29, 0, 11, 'продажам?\nИнтересует не мега-звезда, а именно предметный, руками умеющий продавать сам и помогающий выстраивать это бизнесам.', 459, 0),
(30, 0, 11, '2', 459, 0),
(31, 0, 11, '3', 459, 0),
(36, 0, 11, '3.1', 459, 31),
(37, 1413822798, 11, 'also facing that prob. on the plteform of win 7', 459, 29),
(38, 0, 11, ' here i dont have internet connection.. @Samint Sinha thanks ill check it out maybe tomorrow.', 459, 29),
(39, 0, 11, ' Select max id and you will have dhe last row returned', 459, 29),
(32, 0, 11, '4', 459, 0),
(44, 1414354324, 11, 'How to do', 456, 29),
(45, 1414354469, 11, 'sfsfsf', 456, 29),
(46, 1414354708, 11, 'dddd', 456, 29),
(47, 1414357761, 11, 'sfsfs', 456, 0),
(57, 1414370833, 39, 'kkkppppppp', 456, 0),
(49, 1414358233, 11, 'VSF\nSFSF', 456, 0),
(50, 1414359589, 11, 'How to do', 456, 0),
(51, 1414359660, 11, 'sfsfsdf', 456, 0),
(52, 1414361057, 11, 'SDFSF', 456, 0),
(53, 1414364023, 11, 'dsfdsjfsifmsi', 456, 0),
(54, 1414364031, 11, 'sdfdskjfnskf', 456, 52),
(55, 1414364034, 11, 'sdfdskjfnskf', 456, 52),
(56, 1414364044, 11, 'fndsdfnsofosfi', 456, 52),
(58, 1414370841, 39, 'dfgdfgdgdgdgdgdfgdgdfg', 456, 0);

转储表文章:

CREATE TABLE IF NOT EXISTS `article` (
  `idArticle` int(11) NOT NULL AUTO_INCREMENT,
  `ArticleName` varchar(255) NOT NULL,
  `ArticleTime` int(11) NOT NULL,
  `ArticleDescription` varchar(500) NOT NULL,
  `ArticleText` text NOT NULL,
  `ArticleToUserID` int(11) DEFAULT NULL,
  `ArticleCategory` int(11) NOT NULL,
  `ArticleView` int(11) NOT NULL,
  `ArticleCountry` int(11) NOT NULL,
  `ArticlePhoto` varchar(150) NOT NULL,
  `ArticleCity` int(11) NOT NULL,
  PRIMARY KEY (`idArticle`),
  UNIQUE KEY `idArticle_UNIQUE` (`idArticle`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=50 ;

Answer 1

通过在评论上构建自连接，您不会限制结果，而是会增加结果。您需要的是评论表上的单个子查询

SELECT c.*, ar.ArticleName,
    ar.idArticle, du.DetailToUsersName,
    du.DetailToUsersPhoto, COALECSE(SUM(d.count), 0)
FROM 
    (SELECT *
     FROM 
         commenttoarticle
     WHERE
         CommentToArticleIdArticle = 11
     ORDER BY
         CommentToArticlePID, idCommentToArticle DESC
     LIMIT 3
    ) c
LEFT JOIN 
    article ar
    ON c.CommentToArticleIdArticle = ar.idArticle 
LEFT JOIN
    detailtousers du
    ON du.idDetailToUsers = c.CommentToArticleIdUser 
LEFT JOIN
    likes d
    ON (d.IdNote = c.idCommentToArticle AND d.LikeType = 6)
GROUP BY
    c.idCommentToArticle

我假设一条评论在 Likes 表中可以有多个条目，否则不需要子查询和分组依据。