从子查询 SQL 返回所有行? [英] Return all rows from sub-query SQL?

问题描述

现在我编写了查询 SQL 来获取受第一个查询限制的行:

Now I wrote query SQL for getting rows limited by first query:

SELECT * FROM commenttoarticle a 
WHERE a.idCommentToArticle = (SELECT CommentToArticlePID FROM commenttoarticle b) 
ORDER BY a.idCommentToArticle DESC LIMIT 3 

当我尝试执行此查询时，我得到:

When I try to execute this query, I get:

#1242 - Subquery returns more than 1 row 

如何解决这个问题?所以，我需要从子查询中获取所有行.

How to resolve this issue? So, I need get all rows from sub-query.

如果我想返回一行 - 我需要使用 GROUP BY，但这不是解决方案

If I want return one row - I need use GROUP BY, but it is not solution

修改后的查询:

    SELECT a.idCommentToArticle FROM 
commenttoarticle a WHERE a.CommentToArticlePID IN 
(SELECT idCommentToArticle FROM commenttoarticle b) ORDER BY a.idCommentToArticle DESC LIMIT 3

转储表commenttoarticle:

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 ;

--
-- Дамп данных таблицы `commenttoarticle`
--

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);

结果我需要:

这是 sqlfiddle 的示例:sqlfiddle.com/#!2/dbd82a/1 我需要为每个第一个查询获取具有无限 COMMENTTOARTICLEPID 的最后 3 行(如果存在).例如，我需要使用 IDCOMMENTTOARTICLE: 58, 57, 56, 52

Here is example at sqlfiddle: sqlfiddle.com/#!2/dbd82a/1 I need get last 3 rows with a unlimited COMMENTTOARTICLEPID for each first query, if exists. In example, I need get rows with IDCOMMENTTOARTICLE: 58, 57, 56, 52

推荐答案

我认为您的子查询返回超过 1 行，因此将您的="替换为IN".像这样...

I am thinking that your subquery returns more than 1 row hence replace your "=" with "IN". Like this...

SELECT * FROM commenttoarticle a 
WHERE a.idCommentToArticle IN (SELECT CommentToArticlePID FROM commenttoarticle b) 
ORDER BY a.idCommentToArticle DESC LIMIT 3 

这篇关于从子查询 SQL 返回所有行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！