求助：将三个类别表合并为一张表。sql语句如何写，谢谢大家

Question

原来数据库有三个类别表和一个产品表都是用外键关联
表type1中有2个字段 有2条记录
type1_id,userid 
1         5
2         6表type2中有2个字段 有3条记录
type2_id,type1_id 
1        2
2        1 
3        1 
表type3中有2个字段 有4条记录
type3_id,type2_id 
1          3
2          2
3          2
4          1 产品表p中 有四个字段 有三条记录 0就是没有选下级分类
p_id,type1_id,type2_id,type3_id
1     1         0         0
2     2         1         0
3     1         3         1先在想把三个类别表合为一个类别表category，有四个个字段 p_count类别下边产品的数量
category_id,father_id,all_fatherid,userid,p_count
1             0        ,0,1,         5    2
2             0        ,0,2,         6    1
3             2        ,0,2,3,       6    1
4             1        ,0,1,4,       5    0
5             1        ,0,1,5,       5    1
6             5        ,0,1,5,6,     5    1
7             4        ,0,1,4,7,     5    0
8             4        ,0,1,4,8,     5    0
9             3        ,0,2,3,9,     6    0
同时也在新的产品表中插入数据 表名product,有四个字段 category_id存入的是type3_id或者是type2_id或者type1_id存入category之后的id，但是存入的是不等于0的最低级的idproduct_id,category_id，all_category_id,userid
1               1            ,0,1,         5
2               3            ,0,2,3,       6
3               6            ,0,1,5,6,     5

Answer 1

all_fatherid的意思是记录所有父节点的id,0,+父节点id,+自己idcategory_id,father_id,all_fatherid,userid,p_count
1               0       ,0,1,       5       2
2               0       ,0,2,       6       1
3               2       ,0,2,3,     6       1
4               1       ,0,1,4,     5       0
5               1       ,0,1,5,     5       1
6               5       ,0,1,5,6,   5       1
7               4       ,0,1,4,7,   5       0
8               4       ,0,1,4,8,   5       0
9               3       ,0,2,3,9,   6       0
同时也在新的产品表中插入数据 表名product,有四个字段 category_id存入的是type3_id或者是type2_id或者type1_id存入category之后的id，但是存入的是不等于0的最低级的idproduct_id,category_id，all_category_id,userid
1             1            ,0,1,          5
2             3            ,0,2,3,        6
3             6            ,0,1,5,6,      5

Answer 2

if object_id('tempdb..#t1')>0 drop table #t1
go
if object_id('tempdb..#t2')>0 drop table #t2
go
if object_id('tempdb..#t3')>0 drop table #t3
go
select * into #t1 from (
select 
a.category_id,a.father_id
,ltrim(father_id)+','+ltrim(category_id) all_fatherid
,a.userid,a.oldid  from 
(
select 
category_id=ROW_NUMBER()over(order by type1_id)
,0 father_id
,userid
,type1_id oldid
from type1
) a
) a
go
select * into #t2 from (
select 
a.category_id,a.father_id
,a.all_fatherid+','+ltrim(category_id) all_fatherid
,a.userid,a.oldid  
from (
select 
category_id=ROW_NUMBER()over(order by type2_id)+(select count(*) from type1)
,b.category_id father_id
,type2_id oldid
,all_fatherid
,b.userid
from type2 a
left join #t1 b on a.type1_id=b.oldid
) a
) b
go
select * into #t3 from (
select 
a.category_id,a.father_id
,a.all_fatherid+','+ltrim(category_id) all_fatherid
,a.userid,a.oldid  
from (
select 
category_id=ROW_NUMBER()over(order by a.type3_id)+(select count(*) from type1)+(select count(*) from type2)
,b.category_id father_id
,type3_id oldid
,all_fatherid
,b.userid
from type3 a
left join #t2 b on a.type2_id=b.oldid
) a
) c
goif object_id('category')>0 drop table category
go
select category_id,father_id,all_fatherid,userid,p_count into category from (
select category_id,father_id,all_fatherid,userid,0 p_count from #t1
union all
select category_id,father_id,all_fatherid,userid,0 from #t2
union all
select category_id,father_id,all_fatherid,userid,0 from #t3
) a
goif object_id('tempdb..#p')>0 drop table #p
go
select * into #p from (
select 
p_id
,case when type3_id<>0 then 3
when type2_id<>0 then 2
else 1 end tbname
,case when type3_id<>0 then type3_id
when type2_id<>0 then type2_id
else type1_id end tbid
from p
) a
goif object_id('product')>0 drop table product
go
select * into product from (
select 
p_id product_id 
,category_id
,all_fatherid all_category_id
,userid
from #p
inner join #t1 on tbname=1 and tbid=oldid
union all
select 
p_id product_id 
,category_id
,all_fatherid all_category_id
,userid
from #p
inner join #t2 on tbname=2 and tbid=oldid
union all 
select 
p_id product_id 
,category_id
,all_fatherid all_category_id
,userid
from #p
inner join #t3 on tbname=3 and tbid=oldid
) a
goupdate category
set p_count=b.p_count
from category a,(select category_id,COUNT(*) p_count from p
inner join #t1 on type1_id=oldid
group by category_id
union all 
select category_id,COUNT(*) p_count from p
inner join #t2 on type2_id=oldid
group by category_id
union all 
select category_id,COUNT(*) p_count from p
inner join #t3 on type3_id=oldid
group by category_id) b
where a.category_id=b.category_id
go
select * from category
go
select * from product
go