在mysql中结合两个查询其他问题[重复]

Question

This question is an exact duplicate of:

• sum price of childs in other table mysql 2 answers

i have two query in three table

table 1 = hirarchey of price an users

+----------+------------+-----------+
| userid   |    parent  |    price  |
+----------+------------+------------
| 1        |    null    |      20   | 
| 2        |      1     |      20   | 
| 3        |      1     |      20   | 
| 4        |      2     |      20   | 
| 5        |      2     |      20   | 
| 6        |      3     |      20   | 
| 7        |      4     |      20   | 
+----------+------------+-----------+

I need to get all userid with parent 1 then get descendant in other table and group by userid sum prices

+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           1 |
|           1 |             4 |           2 |
|           1 |             5 |           2 |
|           1 |             6 |           2 |
|           1 |             7 |           3 |
|           2 |             2 |           0 |
|           2 |             4 |           1 |
|           2 |             5 |           1 |
|           2 |             7 |           2 |
|           3 |             3 |           0 |
|           3 |             6 |           1 |
|           4 |             4 |           0 |
|           4 |             7 |           1 |
|           5 |             5 |           0 |
|           6 |             6 |           0 |
|           7 |             7 |           0 |
+-------------+---------------+-------------+

this http://sqlfiddle.com/#!9/9415ed/30 work fine for sum of price of decedent

also in have another table with compute the ranking of result previous query

http://sqlfiddle.com/#!9/b5de18/1

i need to combine two query very thanks

more info about second query in this link compare result with other table mysql

two query result work fine but second query read table user_buys , i will user_buys remove and get from first query

</div>

Answer 1

Just thinking out loud, if you were to use a nested set model instead...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,price INT NOT NULL
,lft INT NOT NULL
,rgt INT NOT NULL
);

INSERT INTO my_table VALUES
(1,20,1,14),
(2,20,2,9),
(3,20,10,13),
(4,20,3,6),
(5,20,7,8),
(6,20,11,12),
(7,20,4,5);

SELECT SUM(x.price) 
  FROM my_table x 
  JOIN my_table y 
    ON y.lft < x.lft 
   AND y.rgt > x.rgt 
 WHERE y.id = 1;
+--------------+
| SUM(x.price) |
+--------------+
|          120 |
+--------------+