创建了一个递归的产品分类表,怎么查询自己要的数据.

[fjjiangyong]

表名:t_Column

字段
ID Fatherid name
1 0 男装
2 0 女装
3 0 背包
4 1 衬衫
6 4 休闲
8 6 长袖
9 2 裙子

从上表可知 男装包含:(衬衫 休闲 长袖)

查询 属于男装类别 的sql语句怎么写???

[疯狂代码]

select * from t_Column where Fatherid = (select id from t_Column where name ='男装'

[wangchangming]

WITH cte AS(
SELECT id,
fatherid,
NAME
FROM t_column
WHERE NAME = N'男装'
UNION ALL
SELECT a.id,
a.fatherid,
a.name
FROM t_Column AS a,
cte AS b
WHERE a.fatherid = b.id
)
SELECT *
FROM cte;

[Ahoo]

这种方法的效率并不高，推荐楼主使用：
TreeID Name
001 男装
001001 背包

这种方式的话 查询数据库 仅使用 like '001%' 即可查询出所有 男装的类别了。

[快溜]

;with cte as
(
select name from t_Column where name='男装'
union all
select a.name from t_Column a join cte b on a.Fatherid=b.id
)

select * from cte

[fjjiangyong]

[Quote=引用 1 楼 的回复:]
;with cte as
(
select name from t_Column where name='男装'
union all
select a.name from t_Column a join cte b on a.Fatherid=b.id
)

select * from cte
[/Quote] 不全.

[Im_yours]

select * from t_Column where Fatherid=(selcet * from name ='男装')
字典表啊~

[Im_yours]

select * from t_Column where Fatherid=(selcet id from name ='男装')
······是这样·

[Im_yours]

[Quote=引用 7 楼 的回复:]
select * from t_Column where Fatherid=(selcet id from name ='男装')
······是这样·
[/Quote]对了忘说了···男装 这个列名要唯一··才行