这个sql怎么写
select
K.ID,
K.Product_ID,
K.Product_Name,
K.Product_Code,
K.Price,
K.Count,
K.StoreRoomArea,
K.CountRes
from
(
select
ROW_NUMBER() OVER (ORDER BY B.Product_ID desc) AS ID,
B.Product_ID,
A.Product_Name,
A.Product_Code,
isnull(C.Product_Price,F.Price) as Price,
B.StoreRoomArea,
(B.Count-B.SaleLockCount-B.AdjustLockCount-B.WDLockCount-B.BackLockCount-convert(float,B.ReserveCount)) as count,
count(*) over() as CountRes
from dbo.XC_Product A
join
(select * from dbo.XC_StoreRoomProduct where StoreRoomID='0001'and StoreRoomArea='0001-01') B
on A.Product_ID=B.Product_ID
left join
(
select top 1* from dbo.XC_Purchase_Detail D
join XC_Purchase E
on E.pur_order_id=D.order_id
order by pur_CreateDate desc)C
on A.Product_ID=C.Product_ID
left join dbo.XC_ProductPrice F
on A.Product_ID=F.Product_ID
where 1=1
)K where K.ID>20*(1-1) and K.ID<=1*20
价格取xc_purchase最后一次插入的值,如果没值则取XC_ProductPrice 得值