求 ;WITH 转 SQL 2000

[lu仙深]

;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM ProductDir AS A
WHERE NOT EXISTS(SELECT * FROM ProductDir WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM ProductDir AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT * FROM T
where status=0 and isCompanyDir =1
ORDER BY T.px,T.DirName



发现这个在SQL 2000 里无法运行，求转
求大神帮忙，先谢过

[xxfvba]

select * from
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM ProductDir AS A
WHERE NOT EXISTS(SELECT * FROM ProductDir WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM ProductDir AS A
JOIN T AS B
ON A.[parentid]=B.id
) T
where status=0 and isCompanyDir =1
ORDER BY T.px,T.DirName

[LongRui888]

你这个是递归的把，在2000需要写一堆的代码，你可以参考一下这个：

在论坛中出现的比较难的sql问题：21(递归问题3)
http://blog.csdn.net/sqlserverdiscovery/article/details/18363633