子查询在多个选择上返回超过1个值的SQL错误



sql-server database (3)

您将通过以下查询得到您想要的结果

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;

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

子查询返回多个值。 当查询出现在=,!=,<,<=,>,> =或子查询用作表达式时,这是不允许的。 该语句已终止。 (0行受影响)

这很奇怪,因为代码中没有错误。 但如果有,请纠正我。

这是查询:

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

Answer #1

尝试这个

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

Answer #2

在SQLServer2005 +中,您可以使用通用表表达式的选项

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行

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的示例





insert