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
------解决方案--------------------
;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
------解决方案--------------------
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
------解决方案--------------------
SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY applyDate DESC ) AS RowNumber