SQL 父子级关系找出最底层的数量和个层级数量验证

表结构即

数据里有lotid和ParentLotId，和level

DECLARE @LotNM NVARCHAR(50)='FJV002122052';--输入需要的条码
WITH CTE_TEMP   
as(
select Qty,Level,LotId,ParentLotId,ParentLotNM FROM lot where LotNM=@LotNM
union ALL
SELECT a.qty,a.Level,a.LotId,a.ParentLotId,a.ParentLotNM  FROM lot a
INNER JOIN CTE_TEMP b ON a.ParentLotId = b.LotId 
)  --递归查出所有的层级关系
select SUM(t1.Qty)
from CTE_TEMP t1
left join CTE_TEMP t2 on t1.LotId=t2.ParentLotId  --通过找到lotid不存在是ParentLotId的，即为最后一层的数量
where t2.LotId is NULL

--通过分组找到个个子级的数量和父级的数量做对比
SELECT ParentLotNM,SUM(qty)'该父级的子级数量和',(SELECT qty FROM lot a WHERE a.LotNM=ISNULL(b.ParentLotNM,@LotNM))'该父级数量' FROM CTE_TEMP b GROUP BY b.ParentLotNM