WITH temp AS
(
SELECT ROW_NUMBER() OVER(ORDER BY BD.FitemId DESC) AS RowNo,POS.*,BD.dalei,BD.zhonglei,BD.xiaolei,BD.xilie ,BD.F2C005 AS Whether,BD.f2c006 AS MOQ,BD.F2C007 AS ReadTime,BD.F2C002 AS OEM,BD.F2C003 AS InternalBuy,BD.fenPdate AS StorageData
,ERP_TB.*,ERP_WL.*
,(cast(cast((((POS.SPRICE)/(ERP_TB.fsaleprice))*100)as
decimal(18,0))as varchar)+'%')zklv,(CAST(CAST((((POS.SPRICE-(ERP_TB.forderprice*1.17))/POS.SPRICE)*100)AS DECIMAL(18,1))AS VARCHAR)+'%')gplv
FROM F2C_MANAGE AS BD --F2C_MANAGE本地基础数据
LEFT JOIN
(SELECT MS.FITEMID,MS.FName , cast(MS.forderprice as
decimal(18,2))FOrderprice, cast(MS.fsaleprice as
decimal(18,2))FSaleprice,MS.f_108 AS BarCode, KS.FQty AS KSF2C,TM.FQty AS TMF2C,F2C.FQty AS F2C,MD.FQty AS MDF2C,TI.FNAME AS Country FROM [ERP].[AIS20150111112601].[dbo].T_ICITEM MS
LEFT JOIN
(SELECT FITEMID,FQty FROM [ERP].[AIS20150111112601].[dbo].ICInventory --ICInventory根据门店号获取ITEMID
WHERE FStockId=112411)KS ON KS.FITEMID=MS.FITEMID
LEFT JOIN
(SELECT FITEMID,FQty FROM [ERP].[AIS20150111112601].[dbo].ICInventory --ICInventory根据门店号获取ITEMID
WHERE FStockId=90823)TM ON TM.FITEMID=MS.FITEMID
LEFT JOIN
(SELECT FITEMID,FQty FROM [ERP].[AIS20150111112601].[dbo].ICInventory --ICInventory根据门店号获取ITEMID
WHERE FStockId=109876)F2C ON F2C.FITEMID=MS.FITEMID
LEFT JOIN
(SELECT FITEMID,FQty FROM [ERP].[AIS20150111112601].[dbo].ICInventory --ICInventory根据门店号获取ITEMID
WHERE FStockId=
(select FItemId from [ERP].AIS20150111112601.dbo.t_Stock
where Fname='特卖仓'))MD ON MD.FITEMID=MS.FITEMID--变量
Left join
(SELECT FNAME,FITEMID FROM [ERP].AIS20150111112601.dbo.t_Item) TI on MS.F_114 = TI.FITEMID
WHERE MS.fsaleprice!=0
) as ERP_TB on BD.FITEMID = ERP_TB.FItemID
LEFT JOIN(
-- 查询POS数据
SELECT SOURCE,SPRICE,PPRICE FROM openquery(POS,'
select t1.*,t2.SALE_QTY from (SELECT
TG.CHAIN_NO,MG.MS_NO,MG.GOODS_CD,MG.GOODS_NM,MS.SOURCE,TG.ERP_CD1 AS ERP_CD
,F_GET_MPRICE(TO_CHAR(SYSDATE,''YYYYMMDD''),''0'',MG.MS_NO,MG.GOODS_CD) AS SPRICE
,F_GET_TPRICE(TO_CHAR(SYSDATE,''YYYYMMDD''),''0'',''C001'',MG.GOODS_CD) AS PPRICE
FROM MGOODSTB MG,TGOODSTB TG,MMSSRCTB MS,MMEMBSTB MP
WHERE MG.MS_NO = ''GC0140''
AND MG.MS_NO = MS.MS_NO
AND MG.GOODS_CD = MS.GOODS_CD
AND TG.CHAIN_NO = ''C001''
AND MG.GOODS_CD = TG.GOODS_CD
AND MG.MS_NO = MP.MS_NO
--过滤保留F2C商品
AND F_GET_MPRICE(TO_CHAR(SYSDATE,''YYYYMMDD''),''0'',MG.MS_NO,MG.GOODS_CD)
<> F_GET_TPRICE(TO_CHAR(SYSDATE,''YYYYMMDD''),''0'',''C001'',MG.GOODS_CD)
--
) t1
left join (
SELECT GOODS_CD,SUM(SG.SALE_QTY) SALE_QTY
FROM SGOODSTB SG
WHERE MS_NO=''GC0140''
and SG.SALE_DATE>=20151028
GROUP BY GOODS_CD
) t2
on t1.goods_cd=t2.goods_cd where SPRICE!=0')
) as POS ON POS.SOURCE = ERP_TB.BarCode
LEFT JOIN (
SELECT ERP.EMPAREA,Uuid,FName AS Model,FModel,FName AS Picture,described,wlName,spColour,guige,yongtu,tedian1,wlGw,daxiao
FROM [LOCK2ERPWLWW].[dbo].[M_WLXZMM] MWM,
[LOCK2ERPWLWW].[dbo].[M_WLXZ] MW,[LOCK2ERPWLWW].[dbo].[CM_EMP] ERP
where MW.PUuid=MWM.PUuid AND (gcNumber='' OR gcNumber IS NULL)
and ERP.EMPID=MW.ApplyUser
) AS ERP_WL ON ERP_WL.Model=ERP_TB.FName and ERP_WL.EMPAREA =BD.WLAREA)
SELECT *
FROM temp
WHERE RowNo>3500 and rowno <4300
如果RowNo只一个 会很快 加其他条件没有问题 但是加上and 或者between 超慢 求教
------解决思路----------------------
给temp表加个RowNo的非聚集索引