易飞指定客户所有产品最新报价明细

最新推荐文章于 2024-06-08 21:38:11 发布

原创最新推荐文章于 2024-06-08 21:38:11 发布 · 927 阅读

CC 4.0 BY-SA版权

本文介绍了一种使用 SQL Server 进行复杂数据检索的方法，通过定义临时表和游标来实现特定条件下的数据筛选与处理流程。此过程涉及从多个表中联接并选择数据，再将结果插入到临时表中进行排序展示。

DECLARE @PHH VARCHAR(40),
@MB001 VARCHAR(40),
@MB002 VARCHAR(60),
@MB003 VARCHAR(200),
@MB004 VARCHAR(10),
@MB008 NUMERIC(17,8),
@MB017 VARCHAR(12)
CREATE TABLE #TEMPDJ
(
MB001 VARCHAR(40),
MB002 VARCHAR(60),
MB003 VARCHAR(200),
MB004 VARCHAR(10),
MB008 NUMERIC(17,8),
MB017 VARCHAR(12)
)
DECLARE PH CURSOR FOR select DISTINCT COPMB.MB002 from COPMB
left join INVMB on COPMB.MB002=INVMB.MB001
WHERE INVMB.MB005='03' AND COPMB.MB001='088'
OPEN PH
FETCH NEXT FROM PH INTO @PHH
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 @MB001= A.MB002,@MB002=B.MB002,@MB003=B.MB003,@MB004=A.MB004,@MB008=A.MB008,@MB017=A.MB017
FROM COPMB A
LEFT JOIN INVMB B ON B.MB001=A.MB002 WHERE A.MB001='088' AND A.MB002=@PHH
ORDER BY A.MB017 DESC
INSERT INTO #TEMPDJ VALUES (@MB001,@MB002,@MB003,@MB004,@MB008,@MB017)

FETCH NEXT FROM PH INTO @PHH
END
CLOSE PH
DEALLOCATE PH
SELECT * FROM #TEMPDJ ORDER by MB017 desc