分层数据中所有子级和父级的总和 [英] Sum of all child and parent in hierarchical data

问题描述

hi
我有2张桌子可供出售以下字段：



saleDetail（id，commodityID，count，price，...）

商品（id，parentID，名称）



商品表是分层的或递归的。



有我的测试数据



商品



id- - parentID--名称

1-- null-- 食物

2-- 1-- 水果 < br $> b $ b 3--2--苹果

4--2--橙色

5--2--甜瓜

6-- 1-- 海鲜食品

7--6--三文鱼

8--6--虾




saleDetail



commodityID-- count- - 价格

3-- 1-- 100

4-- 1-- 150
5-- 1-- 200

7-- 1-- 110

8-- 1-- 180

4-- 1-- 160





我想要一份如下报告：





名称 - 总计

食物 - 900（所有记录的总和）

水果 - 610（3,4,5的总和）

apple-- 100（总和3）

orange-- 310 （4的总和）

甜瓜 - 200（5的总和）

海湾食物 - 290（7,8的总和）

三文鱼 - 110（总和7）

虾 - 180（总和8）



< b>我尝试了什么：



这是我的SQL查询，但不是正确答案：



WITH tree AS

（

- 初始化

SELECT id，parentid，name

FROM商品

WHERE parentID为空

UNION ALL

- 递归执行

SELECT e.id，e.parentid，e.name

来自商品e INNER JOIN树m

ON e.parentID = m.id

）

--SELECT * FROM tree

选择名称，总和（计数*价格）来自树的总额RIGHT OUTER JOIN

saleDetail ON tree.id = saleDetail.commodityID

group by name



结果;没有计算的父母总数



名字 - 总计

apple-- 100

瓜 - 200

橙 - 310

三文鱼 - 110

虾 - 180

hi I have 2 table for sale with below fields:

saleDetail (id, commodityID, count, price, ...)
commodity (id, parentID, name)

commodity table is hierarchical or recursive.

There are my test data

commodity

id-- parentID-- name
1-- null-- foods
2-- 1-- fruits
3-- 2-- apple
4-- 2-- orange
5-- 2-- melon
6-- 1-- sea foods
7-- 6-- salmon
8-- 6-- shrimp


saleDetail

commodityID-- count-- price
3-- 1-- 100
4-- 1-- 150
5-- 1-- 200
7-- 1-- 110
8-- 1-- 180
4-- 1-- 160


I want a report like below:


name-- total
foods-- 900 (sum of all records)
fruits-- 610 (sum of 3, 4, 5)
apple-- 100 (sum of 3)
orange-- 310 (sum of 4)
melon-- 200 (sum of 5)
sea foods-- 290 (sum of 7, 8)
salmon-- 110 (sum of 7)
shrimp-- 180 (sum of 8)

What I have tried:

this is my sql query but not a correct answer:

WITH tree AS
(
--initialization
SELECT id, parentid, name
FROM commodity
WHERE parentID is null
UNION ALL
--recursive execution
SELECT e.id, e.parentid, e.name
FROM commodity e INNER JOIN tree m
ON e.parentID = m.id
)
--SELECT * FROM tree
select name, sum(count*price) as total from tree RIGHT OUTER JOIN
saleDetail ON tree.id = saleDetail.commodityID
group by name

result; that total of parents not calculated

name-- total
apple-- 100
melon-- 200
orange-- 310
salmon-- 110
shrimp-- 180

推荐答案

检查一下：

Check this:

DECLARE @commodity TABLE(id INT IDENTITY(1,1), parentID INT, [name] VARCHAR(30))
INSERT INTO @commodity(parentID, [name])
VALUES(null, 'foods'),
(1, 'fruits'),
(2, 'apple'),
(2, 'orange'),
(2, 'melon'),
(1, 'sea foods'),
(6, 'salmon'),
(6, 'shrimp')


DECLARE @saleDetail TABLE(commodityID INT, [count] INT, price INT)
INSERT INTO @saleDetail (commodityID, [count], price)
VALUES(3, 1, 100),
(4, 1, 150),
(5, 1, 200),
(7, 1, 110),
(8, 1, 180),
(4, 1, 160)

;WITH tree AS
(
	--initial part
	SELECT c.ID, c.parentID, c.[name], sd.[count] AS countOfItems, sd.price
	FROM @saleDetail AS sd INNER JOIN @commodity AS c ON sd.commodityID = c.id 
	UNION ALL
	--recursive part
	SELECT c.ID, c.parentID, c.[name], t.countOfItems, t.price 
	FROM @commodity AS c INNER JOIN tree AS t ON c.id = t.parentID 
)
SELECT t.[name], SUM(t.countOfItems * t.price) AS total
FROM tree AS T
GROUP BY t.[name]

结果：

Result:

name	total
apple	100
foods	900
fruits	610
melon	200
orange	310
salmon	110
sea foods	290
shrimp	180

这篇关于分层数据中所有子级和父级的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！