SQL 父级递归相乘

[greentree_qx]

Create table unit
(
matNo varchar(20) not null,
unitNo varchar(20) not null primary key(matNo,unitNo),
unitName varchar(20),
unitParentNo varchar(20),
unitNum int default 1
)
insert into unit
select '1001','001','箱','',1 union
select '1001','002','包','001',5 union
select '1001','003','袋','002',10 union
select '1002','001','盒','',1 union
select '1002','002','包','001',10 union
select '1002','003','支','002',12

我想要的结果为,查询条件为matNo='1001',unitNo='001',得到的结果为1*5*10=50
matNo='1001',unitNo='002',得到的结果为1*10 =10
使用SQL脚本实现，有知道的大神吗,帮帮看看

[Tiger_Zhao]

WITH tree AS (
    SELECT matNO, 
           unitNO, 
           Convert(varchar(100),unitNum) rExp,
           unitNum rValue,
           1 level
      FROM unit
     WHERE matNo='1001' AND unitNo='001' --查询条件

     UNION ALL

    SELECT u.matNO, 
           u.unitNO, 
           Convert(varchar(100), t.rExp+'*'+Convert(varchar(11),unitNum)) rExp,
           u.unitNum * t.rvalue rValue,
           t.level+1 level
      FROM tree t
      JOIN unit u
        ON u.matNO = t.matNO
       AND u.unitParentNO = t.unitNo
)
SELECT TOP 1
       rExp+'='+Convert(varchar(11),rValue) result
  FROM tree
 ORDER BY level DESC

result
----------------
1*5*10=50

[还在加载中灬]

如果是2005+可以用CTE 如果是2000，就要复杂点

;WITH CTE AS(
	SELECT T1.*,T1.unitNum Result,CAST(T1.unitNum AS VARCHAR(8000)) StrResult FROM unit T1 LEFT JOIN unit T2 ON T1.unitNo=T2.unitParentNo WHERE T2.matNo IS NULL
	UNION ALL
	SELECT T1.*,T1.unitNum*T2.Result,CAST(T1.unitNum AS VARCHAR(8000))+'*'+T2.StrResult FROM unit T1 JOIN CTE T2 ON T1.matNo=T2.matNo AND T1.unitNo=T2.unitParentNo
)
SELECT Result,StrResult+'='+CAST(Result AS VARCHAR(8000))StrResult FROM CTE WHERE matNo='1001'AND unitNo='001'

[习惯性蹭分]

use tempdb 
 Create table unit
(
matNo varchar(20) not null,
unitNo varchar(20) not null primary key(matNo,unitNo),
unitName varchar(20),
unitParentNo varchar(20),
unitNum int default 1
)
insert into unit
select '1001','001','箱','',1 union
select '1001','002','包','001',5 union
select '1001','003','袋','002',10 union
select '1002','001','盒','',1 union
select '1002','002','包','001',10 union
select '1002','003','支','002',12
  
  
declare @matno varchar(50),
        @unit varchar(30)
        
select @matno='1001',
       @unit='001'
  ;with getdown as
  (
   select matno,unitNO,unitParentno,1 as unitNum,1 as rn from unit
   where matno=@matno and unitno=@unit
   union all
   select b.matno,b.unitNO,b.unitParentno,a.unitNum*b.unitNum as unitNum,a.rn+1 as rn from getdown a
   join unit b on b.unitParentNO=a.unitno
   and b.matno=@matno
  )             
  select top 1 matno,unitnUm from getdown  order by rn desc

[greentree_qx]

谢谢各位大神，已经可以了