我编写了下一个查询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


那么,为什么子查询选择中的运算符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 ;

最佳答案

通过在评论上构建自连接,您不仅可以限制结果,还可以将结果相乘。
您需要的是对注释表的单个子查询

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表中具有多个条目,否则子查询和group by不需要。

10-05 20:32
查看更多