结合两个其他问题的MySQL查询 [英] combine two query in mysql of other questions

问题描述

我在三个表中有两个查询

i have two query in three table

表1 =用户的价格等级

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   | 
+----------+------------+-----------+

我需要获取所有具有父代1的用户ID，然后再获取其他表中的子代，并按用户ID总和价格进行分组

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 |
+-------------+---------------+-------------+

此 http://sqlfiddle.com/#!9/9415ed/30按死者的总价罚款

还有另一个用于计算结果先前查询排名的表

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

有关此链接中第二个查询的更多信息将结果与其他表mysql

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

两个查询结果工作正常，但是第二个查询读取了表user_buys，我将user_buys删除并从第一个查询中获取

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

推荐答案

只要大声考虑一下，如果您要使用嵌套集合模型...

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 |
+--------------+

这篇关于结合两个其他问题的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！