WITH recursive
cte AS (SELECT a.*, replace(level, '.', '') leve, ROW_NUMBER() over () - replace(level, '.', '') num, cost
FROM t1129a a
LEFT JOIN t1129b b ON a.ingre = b.ingre),
tmp AS (SELECT item,
ingre,
leve,
qty,
num,
cost * qty cost,
level
FROM cte
WHERE leve in (SELECT max(leve) FROM cte GROUP BY num)
UNION
DISTINCT
SELECT c.item,
c.ingre,
c.leve,
c.qty * t.qty qty,
c.num,
t.cost * c.qty,
c.level
FROM tmp t
LEFT JOIN cte c ON t.num = c.num AND t.leve - 1 = c.leve),
s AS (
SELECT item, ingre, level, qty, cost
FROM tmp
WHERE cost is not null
order by ingre)
SELECT *
FROM s
UNION ALL
SELECT 'A' item, null ingre, null level, null qty, (SELECT sum(cost) FROM s GROUP BY item, level having level = '.1')
SQL每日一题(20211129)要求出每个产品的具体成本是多少
最新推荐文章于 2024-09-27 11:31:35 发布
这篇博客探讨了一种使用WITH recursive语句进行递归查询的方法,并结合LEFT JOIN操作处理复杂的数据层级。通过cte、tmp和s三个CTE(公共表表达式),实现了对数据的层次遍历和成本计算。最终,查询结果不仅包括了每个层级的详细信息,还提供了总成本的汇总。
摘要由CSDN通过智能技术生成