有四个表 BASWL、 STOCK、 ColorMXB、 SizeMXB
其中 BASWL 有 WLID、 WLDH 、WLMCH 、WLLBID
STOCK 有 WLID 、SHL、 COLORID 、SIZEID
ColorMXB 有 MXID、 NAME
SizeMXB 有 MXID 、NAME
* GetFreeItem()是另一个存储过程可以查出KYSHL
其中 BASWL.WLID=STOCK.WLID STOCK.COLORID=COLORMXB.MXID
STOCK.SIZEID=SIZEMXB.MXID
我想查出 WLLBID＝ **** WLDH LIKE ‘%***%’对应的
WLDH WLMCH COLORMXB.NAME SIZEMXB.NAME SUM(SHL) KYSHL
我写的存储过程如下，当我查询 select * from getlbmx(1111,’%cc%’)时查到25个结果却用了21秒，求高手指点或写个简单的，谢谢
IF OBJECT_ID ('dbo.getlbmx') IS NOT NULL
DROp FUNCTION dbo.getlbmx
GO
CREATE FUNCTION getlbmx(@lbid INTEGER ,@wldh VARCHAr (10))
RETURNS @P TABLE(wldh VARCHAr (80),
wlmch VARCHAr (120),COLOR VARCHAr (80),
SIZE VARCHAr (80),
SHL numeric(24,8),
kyshl numeric(24,8) ) AS
BEGIN
DECLARE wlid CURSOR
FOR SELECT wlid FROM BASWL WHERe WLLBID = @lbid AND WLDH LIKE @wldh
OPEN wlid
DECLARE @id INT
FETCH next FROM wlid INTO @id while (@@fetch_status<>-1)
BEGIN
if(@id>0)
BEGIN
INSERT INTO @p (wldh,wlmch,Color,Size,shl,kyshl)
SELECt (SELECT WLDH FROM BASWL WHERe WLID = @ID) AS WLID,
(SELECt WLMCh FROM BASWL WHERe WLID =@ID) AS WLMCh,
(SELECt NAME FROM ColorMXB where MXID = a.colorid) AS Color,
(select NAME from SizeMXB where MXID=a.sizeid) as Size,
sum(SHL) as SHL ,
(select ShL from GetFreeItem() where WLID=@ID and colorID=a.colorid and sizeid=a.sizeid ) as KYSHL
from STOCK a where WLID=@ID
GROUP BY a.colorid ,a.sizeid
end
FETCH next FROM wlid INTO @id
END
CLOSE wlid
DEALLOCATE wlid
RETURN
end
GO