如何使用SQL计算树中值的总和 [英] How to calculate the sum of values in a tree using SQL

问题描述

我需要对一棵用户树获得的每个级别的积分求和.等级 1 是用户低于用户 1 等级的用户积分的总和.Level 2 是用户下面 2 个级别的用户的 Level 1 积分，等等...

I need to sum points on each level earned by a tree of users. Level 1 is the sum of users' points of the users 1 level below the user. Level 2 is the Level 1 points of the users 2 levels below the user, etc...

在非生产服务器上每月计算一次，无需担心性能.

The calculation happens once a month on a non production server, no worries about performance.

SQL 会怎样做?

如果你感到困惑，别担心，我也是！

If you're confused, don't worry, I am as well!

用户表:

ID    ParentID    Points
1     0           230
2     1           150
3     0           80
4     1           110
5     4           54
6     4           342

Tree:
0
|---\
1    3
| \
2  4---
    \  \
     5  6

输出应该是:

ID    Points    Level1     Level2
1     230       150+110    150+110+54+342
2     150
3     80
4     110       54+342
5     54
6     342

SQL Server 语法和函数最好...

SQL Server Syntax and functions preferably...

推荐答案

树不适用于 SQL.如果您有非常(非常)很少的写访问权限，您可以更改树实现以使用嵌套集，这将使此查询非常简单.

Trees don't work well with SQL. If you have very (very very) few write accesses, you could change the tree implementation to use nested sets, that would make this query incredibly easy.

示例(如果我没记错的话):

Example (if I'm not mistaken):

SELECT SUM(points) 
FROM users 
where left > x and right < y 

但是，树上的任何更改都需要接触大量的行.在您的客户端中进行递归可能会更好.

However, any changes on the tree require touching a massive amount of rows. It's probably better to just do the recursion in you client.

这篇关于如何使用SQL计算树中值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！