通过存储过程得到一个SQL语句:
SELECT RowNumberTableSource.*
FROM
(SELECT 1 AS ID,*,ROW_NUMBER() OVER( ORDER BY 流水号 desc) AS RowNumber
FROM view_生产_超领警告 ) AS RowNumberTableSource
WHERE RowNumber BETWEEN 1 AND 15
上面的语句执行需要30秒,在程序里面总是超时
view_生产_超领警告 视图语句如下:
ALTER VIEW [dbo].[view_生产_超领警告]
AS
SELECT tta.流水号,tta.货号,tta.物料代号,ttb.物料名称,tta.最后领料日期,tta.计划数量,tta.领用数量,tta.节料数量,
tta.超领数量,ROUND(tta.超领数量 * ttb.计划单价,2) AS 超料金额
FROM
(
SELECT t1.流水号,t1.货号,t1.物料代号,
ROUND(SUM(ISNULL(t1.数量,0)),2) AS 计划数量,
ROUND(SUM(ISNULL(t3.领用数量,0)),2) AS 领用数量,ROUND(SUM(ISNULL(t4.节料数量,0)),2) AS 节料数量,
ROUND(SUM(ISNULL(t3.领用数量,0)) - SUM(ISNULL(t1.数量,0))-SUM(ISNULL(t4.节料数量,0)),2) AS 超领数量,t3.最后领料日期
FROM
(
SELECT t4.流水号,t4.货号,ISNULL(t4.实际采购代号,t4.物料代号) AS 物料代号,SUM(ISNULL(t4.数量,0)) AS 数量
FROM Table_订单表 t3
--inner join Table_采购_采购单_物料 t2 on t2.流水号 = t3.流水号
INNER JOIN Table_采购_采购计划表 t4 ON t4.流水号 = t3.流水号
WHERE t3.批准时间 > '2012-05-01'
GROUP BY t4.流水号,t4.货号,ISNULL(t4.实际采购代号,t4.物料代号)
) t1 LEFT JOIN Table_生产_超料处理 t2
ON t1.流水号 = t2.流水号 AND t1.货号 = t2.货号 AND t1.物料代号 = t2.物料代号
INNER JOIN
(
SELECT t2.订单流水号 AS 流水号,t2.货号,t2.物料代号,SUM(CASE WHEN t1.红冲 = '否' THEN t2.实发数量 ELSE -t2.实发数量 END) AS 领用数量,MAX(t1.日期) AS 最后领料日期
FROM Table_仓库_领料单 t1 INNER JOIN Table_仓库_领料单_物料 t2 ON t1.领料单编号 = t2.领料单编号
WHERE t1.FlagColumn > 0 AND t2.订单流水号 NOT IN ('FREEITEM','CTRLITEM')
GROUP BY t2.订单流水号,t2.货号,t2.物料代号
) t3 ON t1.流水号 = t3.流水号 AND t1.货号 = t3.货号 AND t1.物料代号 = t3.物料代号
LEFT JOIN
(
SELECT t2.流水号,t2.货号,t2.物料代号,SUM(实收数量) AS 节料数量 FROM Table_仓库_节料退库 t1 INNER JOIN Table_仓库_节料退库_物料 t2 ON t1.节料单号 = t2.节料单号
WHERE ISNULL(t2.流水号,'') <> '' AND t1.FlagColumn = 2
GROUP BY t2.流水号,t2.货号,t2.物料代号
) t4 ON t1.流水号 = t4.流水号 AND t1.货号 = t4.货号 AND t1.物料代号 = t4.物料代号
WHERE t2.流水号 IS NULL
GROUP BY t1.流水号,t1.货号,t1.物料代号,t3.最后领料日期
HAVING SUM(ISNULL(t1.数量,0)) <> SUM(ISNULL(t3.领用数量,0))
) tta
LEFT JOIN Table_工程_物料档案 ttb ON tta.物料代号 = ttb.物料代号
WHERE ROUND(tta.超领数量 * ttb.计划单价,2) > 50
GO
很奇怪,为什么 我用select * from view_生产_超领警告 只需要短短的3秒钟,但是分页需要30秒?求详解
