Subquery returns more than 1 value SQL error on mu

问题:

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

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.

Here's the query:

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

回答1:

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 

See example on SQLFiddle

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

回答2:

You'll get your desired result with the following query

SQLFIDDLE

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

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

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

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

where a.RowNum = b.RowNum-3
and a.RowNum = c.RowNum-6;

Select * from @tblKeywords;

回答3:

try this

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

INSERT  INTO @tblKeywords
        ( Keyword1 ,
          Keyword2 ,
          Keyword3
        )
        SELECT  k ,
                ( SELECT    kk
                  FROM      ( SELECT    e.Keyword AS kk ,
                                        ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum1
                              FROM      t_SMSKeyword AS e
                            ) AS Emp1
                  WHERE     Emp1.RowNum1 = ( RowNum + 3 )
                ) ,
                ( SELECT    kkk
                  FROM      ( SELECT    e.Keyword AS kkk ,
                                        ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum1
                              FROM      t_SMSKeyword AS e
                            ) AS Emp1
                  WHERE     Emp1.RowNum1 = ( RowNum + 6 )
                )
        FROM    ( SELECT    e.Keyword AS k ,
                            ROW_NUMBER() OVER ( ORDER BY e.KeywordID ) AS RowNum
                  FROM      t_SMSKeyword AS e
                ) AS mytable
        WHERE   mytable.RowNum BETWEEN 1 AND 3







SELECT  *
FROM    @tblKeywords