哪位老大能帮忙把下面存储过程转化成mysql 5的,非常感谢,搞了一天了,怎么都搞不通再次感谢啊。create PROCEDURE [dbo].[usp_GetProductCatalogLeafFromRoot]
@Root nvarchar(50),@MaxDepth int=10
AS
CREATE TABLE #TMPPARENT
(
PARENT NVARCHAR(50),
CHILD NVARCHAR(50),
DEPTH DECIMAL(18,0),
ISMID DECIMAL(18,0)
)
CREATE TABLE #TMPCHILD
(
PARENT NVARCHAR(50),
CHILD NVARCHAR(50),
DEPTH DECIMAL(18,0),
ISMID DECIMAL(18,0)
)DECLARE @ISFINISH INT
DECLARE @DEPTH INT
SET @ISFINISH=0
SET @DEPTH=1
INSERT INTO #TMPPARENT(PARENT,CHILD,DEPTH,ISMID)
SELECT DISTINCT PPCCODE,PCCODE,@DEPTH,0 FROM DBO.TBLPCATALOG
WHERE PPCCODE=@Root
WHILE @ISFINISH=0 AND @DEPTH<@MaxDepth
BEGIN
INSERT INTO #TMPCHILD(PARENT,CHILD,DEPTH,ISMID)
SELECT DISTINCT PPCCODE,PCCODE,@DEPTH +1,0 FROM DBO.TBLPCATALOG,#TMPPARENT
WHERE PPCCODE=CHILD AND ISMID=0
UPDATE #TMPPARENT SET ISMID=1
FROM #TMPPARENT,#TMPCHILD
WHERE #TMPPARENT.CHILD=#TMPCHILD.PARENT
INSERT INTO #TMPPARENT (PARENT,CHILD,DEPTH,ISMID)
SELECT DISTINCT PARENT,CHILD,DEPTH,ISMID FROM #TMPCHILD
IF((SELECT COUNT(*) FROM #TMPCHILD)=0)
BEGIN
SET @ISFINISH=1
END
ELSE
BEGIN
TRUNCATE TABLE #TMPCHILD
SET @DEPTH=@DEPTH+1
END
END
--SELECT @Root,PARENT,CHILD,DEPTH,ISMID FROM #TMPPARENT WHERE ISMID =0 ORDER BY DEPTH ASC,ISMID ASC
SELECT A.* FROM TBLPCATALOG A,#TMPPARENT B WHERE A.PCCODE=B.CHILD AND B.ISMID =0 ORDER BY B.DEPTH ASC,B.ISMID ASC,A.PCCODE ASCDROP TABLE #TMPPARENT
DROP TABLE #TMPCHILD
@Root nvarchar(50),@MaxDepth int=10
AS
CREATE TABLE #TMPPARENT
(
PARENT NVARCHAR(50),
CHILD NVARCHAR(50),
DEPTH DECIMAL(18,0),
ISMID DECIMAL(18,0)
)
CREATE TABLE #TMPCHILD
(
PARENT NVARCHAR(50),
CHILD NVARCHAR(50),
DEPTH DECIMAL(18,0),
ISMID DECIMAL(18,0)
)DECLARE @ISFINISH INT
DECLARE @DEPTH INT
SET @ISFINISH=0
SET @DEPTH=1
INSERT INTO #TMPPARENT(PARENT,CHILD,DEPTH,ISMID)
SELECT DISTINCT PPCCODE,PCCODE,@DEPTH,0 FROM DBO.TBLPCATALOG
WHERE PPCCODE=@Root
WHILE @ISFINISH=0 AND @DEPTH<@MaxDepth
BEGIN
INSERT INTO #TMPCHILD(PARENT,CHILD,DEPTH,ISMID)
SELECT DISTINCT PPCCODE,PCCODE,@DEPTH +1,0 FROM DBO.TBLPCATALOG,#TMPPARENT
WHERE PPCCODE=CHILD AND ISMID=0
UPDATE #TMPPARENT SET ISMID=1
FROM #TMPPARENT,#TMPCHILD
WHERE #TMPPARENT.CHILD=#TMPCHILD.PARENT
INSERT INTO #TMPPARENT (PARENT,CHILD,DEPTH,ISMID)
SELECT DISTINCT PARENT,CHILD,DEPTH,ISMID FROM #TMPCHILD
IF((SELECT COUNT(*) FROM #TMPCHILD)=0)
BEGIN
SET @ISFINISH=1
END
ELSE
BEGIN
TRUNCATE TABLE #TMPCHILD
SET @DEPTH=@DEPTH+1
END
END
--SELECT @Root,PARENT,CHILD,DEPTH,ISMID FROM #TMPPARENT WHERE ISMID =0 ORDER BY DEPTH ASC,ISMID ASC
SELECT A.* FROM TBLPCATALOG A,#TMPPARENT B WHERE A.PCCODE=B.CHILD AND B.ISMID =0 ORDER BY B.DEPTH ASC,B.ISMID ASC,A.PCCODE ASCDROP TABLE #TMPPARENT
DROP TABLE #TMPCHILD
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货