多次选择时，子查询返回多个值SQL错误

问题描述

我想要一个临时表，它将使用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

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

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

查看示例

在第一列中选​​择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

第二个解决方案示例

Example for second solution SQLFiddle

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