SELECT *,(QTY-(ISNULL(MRTQTY,1)-ISNULL(PRTQTY,1))) L_LOCQTY,((ISNULL(ANTQTY,1)-ISNULL(VRTQTY,1))+ISNULL(PCTQTY,1)-QTY) L_JOBQTY,(STOCK-(QTY-(ISNULL(MRTQTY,1)-ISNULL(PRTQTY,1)))) L_STKQTY FROM HC_RESVT WHERE WO='090845' OR JOBNO='090845' ORDER BY LOC
我实在是优化不了了。。但是它执行起来还是需要2分钟才能找到。但是程序默认的时间是30秒过了就超时了。也试过改时间。但是改了没有作用还是查询了1分多钟就超时了。实在是没办法了。下面是我视图的SQL
SELECT TOP (100) PERCENT (CASE WHEN MASTERJOB = '' THEN JOBNO ELSE MASTERJOB END) AS WO, JOBNO, CHILDJOB, LOC, PRODLOC, CODE, PLOT,
(SELECT TOP (1) WT
FROM dbo.PROUDF
WHERE (CODE = dbo.RESVT.CODE)) AS WT,
(SELECT TOP (1) MN
FROM dbo.PROUDF AS PROUDF_1
WHERE (CODE = dbo.RESVT.CODE)) AS MN,
(SELECT TOP (1) DN
FROM dbo.PROUDF AS PROUDF_1
WHERE (CODE = dbo.RESVT.CODE)) AS DN, BDESC, CONVERT(CHAR(10), DDATE, 120) AS DDATE, QTY,
(SELECT TOP (1) QTY
FROM dbo.JOBT
WHERE (JOBNO = dbo.RESVT.CHILDJOB) AND (CODE = dbo.RESVT.CODE) AND (PLOT = dbo.RESVT.PLOT)) AS MFQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.V_LOCQTY
WHERE (CODE = dbo.RESVT.CODE) AND (LOC BETWEEN 'S01' AND 'S15') AND (CO = 'HC')) AS STOCK,
(SELECT SUM(QTY) AS Expr1
FROM dbo.HC_POT
WHERE (WO = (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS POTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.HC_ANT
WHERE (WO = (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS ANTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.HC_VRT
WHERE (WO = (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS VRTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.PDT
WHERE ((CASE WHEN PDT.MASTERJOB = '' THEN PDT.JOBNO ELSE PDT.MASTERJOB END)
= (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS PDTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.PCT
WHERE ((CASE WHEN PCT.MASTERJOB = '' THEN PCT.JOBNO ELSE PCT.MASTERJOB END)
= (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS PCTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.MRT
WHERE ((CASE WHEN MRT.MASTERJOB = '' THEN MRT.JOBNO ELSE MRT.MASTERJOB END)
= (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS MRTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.PRT
WHERE (JOBNO = dbo.RESVT.JOBNO) AND (CODE = dbo.RESVT.CODE)) AS PRTQTY
FROM dbo.RESVT
ORDER BY WO, JOBNO
我实在是优化不了了。。但是它执行起来还是需要2分钟才能找到。但是程序默认的时间是30秒过了就超时了。也试过改时间。但是改了没有作用还是查询了1分多钟就超时了。实在是没办法了。下面是我视图的SQL
SELECT TOP (100) PERCENT (CASE WHEN MASTERJOB = '' THEN JOBNO ELSE MASTERJOB END) AS WO, JOBNO, CHILDJOB, LOC, PRODLOC, CODE, PLOT,
(SELECT TOP (1) WT
FROM dbo.PROUDF
WHERE (CODE = dbo.RESVT.CODE)) AS WT,
(SELECT TOP (1) MN
FROM dbo.PROUDF AS PROUDF_1
WHERE (CODE = dbo.RESVT.CODE)) AS MN,
(SELECT TOP (1) DN
FROM dbo.PROUDF AS PROUDF_1
WHERE (CODE = dbo.RESVT.CODE)) AS DN, BDESC, CONVERT(CHAR(10), DDATE, 120) AS DDATE, QTY,
(SELECT TOP (1) QTY
FROM dbo.JOBT
WHERE (JOBNO = dbo.RESVT.CHILDJOB) AND (CODE = dbo.RESVT.CODE) AND (PLOT = dbo.RESVT.PLOT)) AS MFQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.V_LOCQTY
WHERE (CODE = dbo.RESVT.CODE) AND (LOC BETWEEN 'S01' AND 'S15') AND (CO = 'HC')) AS STOCK,
(SELECT SUM(QTY) AS Expr1
FROM dbo.HC_POT
WHERE (WO = (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS POTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.HC_ANT
WHERE (WO = (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS ANTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.HC_VRT
WHERE (WO = (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS VRTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.PDT
WHERE ((CASE WHEN PDT.MASTERJOB = '' THEN PDT.JOBNO ELSE PDT.MASTERJOB END)
= (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS PDTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.PCT
WHERE ((CASE WHEN PCT.MASTERJOB = '' THEN PCT.JOBNO ELSE PCT.MASTERJOB END)
= (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS PCTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.MRT
WHERE ((CASE WHEN MRT.MASTERJOB = '' THEN MRT.JOBNO ELSE MRT.MASTERJOB END)
= (CASE WHEN RESVT.MASTERJOB = '' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)) AND (CODE = dbo.RESVT.CODE))
AS MRTQTY,
(SELECT SUM(QTY) AS Expr1
FROM dbo.PRT
WHERE (JOBNO = dbo.RESVT.JOBNO) AND (CODE = dbo.RESVT.CODE)) AS PRTQTY
FROM dbo.RESVT
ORDER BY WO, JOBNO
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281