求 ;WITH 转 SQL 2000

lu仙深 2015-10-13 09:25:26
;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 里无法运行,求转
求大神帮忙,先谢过
...全文
67 点赞 收藏 2
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复

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

在论坛中出现的比较难的sql问题:21(递归问题3)
http://blog.csdn.net/sqlserverdiscovery/article/details/18363633
回复
xxfvba 2015-10-13
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


回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-10-13 09:25
社区公告
暂无公告