mysql 树形汇总_SQL中的树状数据整理（Mysql）

I have two tables in my database

Table A with columns user_id, free_data, used_data

Table B with columns donor_id, receptor_id, share_data

Basically, a user (lets call x) has some data in his account which is represented by his entry in table A. The data is stored in free_data column. He can donate data to any other user (lets call y), which will show up as an entry in Table B. The same amount of data gets deducted from the user x free_data column.

While entry in Table B gets created, an entry in Table A for user y is also created with free_data value equal to share_data. Now user y can give away data to user z & the process continues.

Each user keep using their data & the entry used_data in Table A keeps on adding up to indicate how much data each user has used.

This is like a tree structure where there is a an entry with all the data (root node) who eventually gives data to others who in-turn give data to other nodes.

Now I would like to write an sql query such that, given a node x (id of entry in Table A), I should be able to sum up total data x has given & who all are beneficiaries at multiple level, all of their used_data need to be collated & showed against x.

Basically, I want to collate

Overall data x has donated.

How much of the donated data from x has been used up.

While the implementation is more graph-like, I am more interested to know if we assume it to be a tree below node x & can come up with a single sql query to be able to get the data I need.

Example

Table A

user_id, free_data, used_data

1 50 10

2 30 20

3 20 20

Table B

donor_id, receptor_id, share_data

1 2 30

1 3 20

Total data donated by 1 - 30 + 20 = 50

Total donated data used - 20 + 20 = 40

This is just one level where 1 donated to 2 & 3. 2 in turn could donated to 4 & all that data needed to be collated in a bubbled up fashion for calculating the overall donated data usage.

解决方案

Yes its possible using a nested set model. There's a book by Joe Celko that describes but if you want to get straight into it there's an article that talks about it. Both the collated data that you need can be retrieved by a single select statement like this:

SELECT * FROM TableB where left > some_value1 and right < some_value2

In the above example to get all the child nodes of "Portable Electronics" the query will be:

SELECT * FROM Electronics WHERE `left` > 10 and `right` < 19

The article describes how the left and right columns should be initialised.