一级分类 ID select id from a where fatherid = 0
二 级分类 ID select id from a where fatherid in(select id from a where fatherid = 0)
下面的查询就是你要的最终结果
select t3.id as first_id,t3.name as first_name,t2.id as second_id,t2.id as second_name,t1.id as third_id,t1.name as third_name from a T1
left join a t2 on t2.id = t1.fatherid
left join a t3 on t3.id = t2.fatherid
where t1.id not in(select id from a where fatherid in(select id from a where fatherid = 0))
如果你有表 B
那么就是
insert into b values(f_id,f_name,s_id,s_name,t_id,t_name)
select t3.id as first_id,t3.name as first_name,t2.id as second_id,t2.id as second_name,t1.id as third_id,t1.name as third_name from a T1
left join a t2 on t2.id = t1.fatherid
left join a t3 on t3.id = t2.fatherid
where t1.id not in(select id from a where fatherid in(select id from a where fatherid = 0))
不过一般不建议你这么多
1, 这样做 下级分类就只能3级了
2,对表B的更新会比较频繁
建议你做一个表值函数 随时用 随时调就可以
温馨提示:内容为网友见解,仅供参考