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