sql从一个表查询数据，插入到另一个表中

Question

已知表a,如左表。需要实现下表
编写sql语言
为了得到下表b

Answer 1

一级分类 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的更新会比较频繁

建议你做一个表值函数 随时用 随时调就可以

Answer 2

select * into 临时表_1级目录 from 表A where fatherid=0
select * into 临时表_2级目录 from 表A a inner join 临时表_1级目录 b on a.fatherid=b.id
select * into 临时表_3级目录 from 表A a inner join 临时表_2级目录 b on a.fatherid=b.id

select * from
(select id as first_id,name as first_name from 临时表_1级目录) a,
(select id as second_id,name as second_name from 临时表_2级目录) b,
(select id as third_id,name as third_name from 临时表_3级目录) c