为什么运算符 LIMIT 不能正常工作? [英] Why operator LIMIT does not work correctly?

问题描述

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

I wrote the next query SQL for getting last comments with re-comments:

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

So, why operator LIMIT 3 in sub-query select does not work? Now this query shows all rows from table commenttoarticle

我似乎需要做一些像这样的事情:

I seem that need do somethink like as:

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 ;

推荐答案

通过在评论上构建自联接，您不会限制结果，而是乘以结果.您需要的是评论表上的单个子查询

By constructing a self join on comments yuo're not limiting but multiply the result. What you need is a single subquery on comments table

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 表中可以有多个条目，否则不需要子查询和分组依据.

I have made the assumption that a comment can have multiple entries in the likes table, otherwise the subquery and the group by is not necesarry.

这篇关于为什么运算符 LIMIT 不能正常工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！