SQL SERVER RowNumber问题

[Believetoy]

SELECT *,(ROW_NUMBER() OVER (ORDER BY applyDate DESC)) AS RowNumber FROM ( select * from Tb_AssetPurchaseApply where 1=1 and ApplyUser='198' and ApplyTypeID=1 and TypeID=1 union select a.* from Tb_AssetPurchaseApply a, Tb_HS_WorkFlowInstance thwfi where a.ApplyCode=thwfi.SampleCode and thwfi.WorkflowGUId='7536413c-7604-4f54-a67f-4e2e930e5af7' AND (thwfi.OperatorID='198' or (thwfi.OperatorUserId='198' and thwfi.[Status]<>1 or (thwfi.[Status]=1 and isBack=1) ) ) ) as tb_Apply where
RowNuner >1 AND RowNumber<8

[Believetoy]

现在一直报错~ 列名 RowNumber无效。。。我蛋疼了。 我在外层里面有as RowNumber 为什么在下面使用的时候还报错呢？？？？ 希望那个大神帮我解决下。。谢谢

[ai_li7758521]

;WITH CTE AS
(
	SELECT *,(ROW_NUMBER() OVER (ORDER BY applyDate DESC)) AS RowNumber 
	FROM 
	( 
		... 
	) as tb_Apply 
)

SELECT *
FROM CTE
WHERE RowNuner >1 AND RowNumber<8

[Believetoy]

他这个是个什么问题呀？？？

[發糞塗牆]

sql的执行顺序是先where再select的，where中还没出现rownumber，所以不存在这列

SELECT  * ,
        ( ROW_NUMBER() OVER ( ORDER BY applyDate DESC ) ) AS RowNumber
FROM    ( SELECT    *
          FROM      Tb_AssetPurchaseApply
          WHERE     1 = 1
                    AND ApplyUser = '198'
                    AND ApplyTypeID = 1
                    AND TypeID = 1
          UNION
          SELECT    a.*
          FROM      Tb_AssetPurchaseApply a ,
                    Tb_HS_WorkFlowInstance thwfi
          WHERE     a.ApplyCode = thwfi.SampleCode
                    AND thwfi.WorkflowGUId = '7536413c-7604-4f54-a67f-4e2e930e5af7'
                    AND ( thwfi.OperatorID = '198'
                          OR ( thwfi.OperatorUserId = '198'
                               AND thwfi.[Status] <> 1
                               OR ( thwfi.[Status] = 1
                                    AND isBack = 1
                                  )
                             )
                        )
        ) AS tb_Apply
WHERE   RowNuner > 1
        AND ( ROW_NUMBER() OVER ( ORDER BY applyDate DESC ) ) < 8

[Mr_Nice]

        
        SELECT  *
        FROM    ( SELECT    * ,
                            ROW_NUMBER() OVER ( ORDER BY applyDate DESC ) AS RowNumber
                  FROM      ( SELECT    *
                              FROM      Tb_AssetPurchaseApply
                              WHERE     1 = 1
                                        AND ApplyUser = '198'
                                        AND ApplyTypeID = 1
                                        AND TypeID = 1
                              UNION
                              SELECT    a.*
                              FROM      Tb_AssetPurchaseApply a ,
                                        Tb_HS_WorkFlowInstance thwfi
                              WHERE     a.ApplyCode = thwfi.SampleCode
                                        AND thwfi.WorkflowGUId = '7536413c-7604-4f54-a67f-4e2e930e5af7'
                                        AND ( thwfi.OperatorID = '198'
                                              OR ( thwfi.OperatorUserId = '198'
                                                   AND thwfi.[Status] <> 1
                                                   OR ( thwfi.[Status] = 1
                                                        AND isBack = 1
                                                      )
                                                 )
                                            )
                            ) AS tb_Apply
                ) T   --少了一层
        WHERE   RowNumber > 1
                AND RowNumber < 8

[-Tracy-McGrady-]

用个括号把where后面的括起来，select * from ()where rownumber... where 后面的只能是真实存在表，子查询等的真实字段。

[Believetoy]

谢谢大神，我已经解决了