多次选择时，子查询返回多个值SQL错误 [英] Subquery returns more than 1 value SQL error on multiple selection

问题描述

我想要一个临时表，它将使用select语句插入值。但是每次运行查询时，我总是遇到错误。

I wanted to have a temporary table that will insert values using select statements. But every time I ran the query, I always got error

子查询返回的值超过1。当查询遵循=，！=，<，< =，>，> =时，或当子查询用作表达式时，不允许这样做。语句已终止。 （0 row（s）affected）

Subquery returns more than 1 value. This is not permitted when the query follows =, !=, <, <=, >, >=, or when the subquery is used as an expression. The statement has been terminated. (0 row(s) affected)

这是奇怪的原因似乎没有错误的代码。但如果有，请纠正我。

It's weird cause it seems there's no errors in the code. But if there is, please correct me.

这是查询：

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))

Insert into @tblKeywords (Keyword1, Keyword2, Keyword3)
Values(
(Select k from 
(Select Keyword k, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 1 and 3),

(Select kk from 
(Select Keyword kk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 4 and 6),

(Select kkk from 
(Select Keyword kkk, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum from t_SMSKeyword) as mytable
where mytable.RowNum BETWEEN 7 and 9)
)

Select * from @tblKeywords

推荐答案

在SQLServer2005 +使用选项与常用表表达式

In SQLServer2005+ You can use option with common table expression

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))

;WITH cte AS
 (      
  SELECT Keyword, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum       
  FROM dbo.t_SMSKeyword  
  )
  INSERT @tblKeywords(Keyword1, Keyword2, Keyword3)
  SELECT c1.Keyword, c2.Keyword, c3.Keyword
  FROM cte c1 JOIN cte c2 ON c1.RowNum + 3 = c2.RowNum
              JOIN cte c3 ON c2.RowNum + 3 = c3.RowNum
  WHERE c1.RowNum BETWEEN 1 and 3 

查看 SQLFiddle 示例

在第一列中选​​择4行，在其他列中选择3行

Select 4 rows in the first column and 3 rows for the other columns

DECLARE @tblKeywords TABLE (Keyword1 VARCHAR(MAX), Keyword2 VARCHAR(MAX), Keyword3 VARCHAR(MAX))
;WITH cte AS
 (      
  SELECT Keyword, ROW_NUMBER() OVER (ORDER BY KeywordID) AS RowNum       
  FROM dbo.t_SMSKeyword  
  )
  INSERT @tblKeywords(Keyword1, Keyword2, Keyword3)
  SELECT c1.Keyword, c2.Keyword, c3.Keyword
  FROM cte c1 LEFT JOIN cte c2 ON c1.RowNum + 4 = c2.RowNum AND c2.RowNum < 8
              LEFT JOIN cte c3 ON c2.RowNum + 3 = c3.RowNum
  WHERE c1.RowNum BETWEEN 1 and 4

SELECT *
FROM @tblKeywords

第二个解决方案示例 SQLFiddle

Example for second solution SQLFiddle

这篇关于多次选择时，子查询返回多个值SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！