I have stored procedure.SELECT statement is like below
我有存储过程.SELECT语句如下所示
select
*, row_number() OVER (ORDER BY ProductRN.ID) as RNN
from (
SELECT
p.ID
,p.ProductCode
,p.ProductNameF
,p.ProductNameE
,p.[Weight]
,p.PackageTypeID
,p.ISForeign
,p.BrandID
,b.BrandNameF
,b.BrandNameE
,p.ImgID
,p.CategoryID
,p.AmountInBox
,pc.PropertyGroupID
,p.Comment
,p.CompanyID
,p.Status
,po.CompanyNameF
,PriceConsumer=MKT.Fn_GetProductPrice(1,p.ID, @CurDate)
,PriceRetail=MKT.Fn_GetProductPrice(2,p.ID, @CurDate)
,PriceWholesale=MKT.Fn_GetProductPrice(3,p.ID, @CurDate)
,pg.PropertyName as PropertyGroupName
,b.LogoID
from mkt.Product as p (nolock)
inner join mkt.ProductCategory as pc (nolock) on pc.ID = p.CategoryID
inner join mkt.Brand b (nolock) on p.BrandID=b.ID
inner join MKT.ProductOwner as po (nolock) on po.ID = b.BrandOwnerID
left join MKT.PropertyGroup as pg (nolock) on pc.PropertyGroupID=pg.ID
) AS ProductRN
where ProductRN.CompanyID=@CompanyID
and ProductRN.Status=1
and ProductRN.CategoryID=@ProductCategoryID
AND (ProductRN.BrandID=@BrandID OR @BrandID=0)
--SOME WAY TO FILTER RNN
end
there is not any way to use RNN Column in WHERE CLAUSE because we can't use Column aliases and window function inside WHERE CLAUSE. actually the problem is that when i put
在WHERE CLAUSE中没有任何方法可以使用RNN列，因为我们不能在WHERE CLAUSE中使用列别名和窗口函数。实际上问题是我放的时候
row_number() OVER (ORDER BY ProductRN.ID) as RNN
inside inner SELECT statement, RNN column exactly become equal to p.ID (Product.ID) it seems sql server first calculates row_number() and then perform the WHERE CLAUSE.
在内部SELECT语句中，RNN列完全等于p.ID（Product.ID），似乎sql server首先计算row_number（）然后执行WHERE CLAUSE。
How can I change this query so that row_num() apply to final query records and I can filter it in WHERE CLAUSE.
如何更改此查询以便row_num（）应用于最终查询记录，我可以在WHERE CLAUSE中对其进行过滤。
1 个解决方案
#1
4
Use a subquery or CTE:
使用子查询或CTE：
select t.*
from (<your query goes here>) t
where rnn = 1;
#1
4
Use a subquery or CTE:
使用子查询或CTE：
select t.*
from (<your query goes here>) t
where rnn = 1;