产品BOM结构的递归查询，查询到最底层物料

数据表中数据如下：

父级   子级   用量
A      B     1
B      C     2
C      D     1
B1     C1    1
X      B     2
X      B1    2

希望得到以下结果：
顶层   子层  用量    标识 
A      B     1
A      C     2
A      D     2    底层
X      B     2
X      B1    2
X      C1    2    底层
X      C     4
X      D     4    底层

谢谢各位大侠！

8 个解决方案

#1

create table sx
(父级 varchar(5),子级 varchar(5),用量 int)

insert into sx
 select 'A','B',1 union all
 select 'B','C',2 union all
 select 'C','D',1 union all
 select 'B1','C1',1 union all
 select 'X','B',2 union all
 select 'X','B1',2


with t as
(select a.父级 '顶层',a.子级,a.用量
  from sx a
  where not exists(select 1 from sx b where b.子级=a.父级)
 union all
 select d.顶层,c.子级,c.用量*d.用量
  from sx c
  inner join t d on c.父级=d.子级
)
select a.顶层,a.子级,a.用量,
       case when not exists(select 1 from sx b where b.父级=a.子级)
            then '底层' else '' end '标识'
 from t a
 order by a.顶层

/*
顶层    子级    用量       标识
----- ----- ----------- ----
A     B      1           
A     C      2           
A     D      2           底层
X     B      2           
X     B1     2           
X     C1     2           底层
X     C      4           
X     D      4           底层

(8 row(s) affected)
*/

#2

怎么执行SQL提示错误
消息 102,级别15，状态1,第12行 't' 附近有语法错误

#3

引用 2 楼 shixin333 的回复:

怎么执行SQL提示错误
消息 102,级别15，状态1,第12行 't' 附近有语法错误

with 前面加个分号 ; 试试

#4

try this,

create table sx
(父级 varchar(5),子级 varchar(5),用量 int)
 
insert into sx
 select 'A','B',1 union all
 select 'B','C',2 union all
 select 'C','D',1 union all
 select 'B1','C1',1 union all
 select 'X','B',2 union all
 select 'X','B1',2
 
 
;with t as
(select a.父级 '顶层',a.子级,a.用量
  from sx a
  where not exists(select 1 from sx b where b.子级=a.父级)
 union all
 select d.顶层,c.子级,c.用量*d.用量
  from sx c
  inner join t d on c.父级=d.子级
)
select a.顶层,a.子级,a.用量,
       case when not exists(select 1 from sx b where b.父级=a.子级)
            then '底层' else '' end '标识'
 from t a
 order by a.顶层;

#5

加了分号还是错误

#6

引用 5 楼 shixin333 的回复:

加了分号还是错误

在SQL2008R2,SQL2005环境测试正常,难道LZ环境是SQL2000?

#7

SQL 2000没有CTE
在SQL 2008中测试OK,谢谢  @唐诗三百首

#8

@唐诗三百首 我在orcle11 G 中运行code  报错，请问下这个是什么原因？