476
社区成员
发帖
与我相关
我的任务
分享连接到其他表乘以mysql中的循环外键
我有2个表一个节点和关系和第二个商店购买每个节点.我的代码工作正常但是当每个节点结果的多次购买乘以购买次数时.
表节点
+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 1 | 4 | 1 |
| 1 | 5 | 2 |
| 1 | 6 | 3 |
| 1 | 7 | 4 |
| 2 | 2 | 0 |
| 2 | 3 | 1 |
| 2 | 5 | 1 |
| 2 | 6 | 2 |
| 2 | 7 | 3 |
| 3 | 3 | 0 |
| 4 | 4 | 0 |
| 5 | 5 | 0 |
| 5 | 6 | 1 |
| 5 | 7 | 2 |
| 6 | 6 | 0 |
| 6 | 7 | 1 |
| 7 | 7 | 0 |
+-------------+---------------+-------------+
表买
+-------------+---------------+-------------+
| userid | amount |
+-------------+---------------+-------------+
| 2 | 1500 |
| 7 | 2000 |
+-------------+---------------+-------------+
mysql代码
SELECT
DISTINCT users.descendant_id ,
SUM(CASE WHEN ances.ancestor_id = buys_ances.userid THEN 1 ELSE 0 END) level_compress
FROM webineh_prefix_nodes_paths as users
join webineh_user_buys as buys on (users.descendant_id = buys.userid )
join webineh_prefix_nodes_paths as ances on (users.descendant_id = ances.descendant_id )
join webineh_user_buys as buys_ances on (buys_ances.userid = ances.ancestor_id )
WHERE users.ancestor_id = 1
and
(SELECT SUM(g2.amount) as amount FROM webineh_user_buys g2 where g2.userid = ances.ancestor_id group by g2.userid ) >= 1000
and
(SELECT SUM(g1.amount) as amount FROM webineh_user_buys g1 where g1.userid = users.descendant_id group by g1.userid ) >= 1000
group by buys.userid ,ances.ancestor_id
在购买数据时的结果
users.descendant_id | users.ancestor_id | level_compress
2 | | 1
6 | | 2
我需要显示ancestor_id压缩的id
...全文
请发表友善的回复…
发表回复
weixin_38061073 2019-09-12
- 打赏
- 举报
增加的金额问题很容易解决.您只需使用首先进行计算的派生表(group by),然后将该(派生表)连接到另一个存储层次结构的表.
要使用的派生表：
( SELECT userid,
SUM(amount) AS sele_descendant_amount
FROM webineh_user_buys
GROUP BY userid
HAVING SUM(amount) >= 1000
) AS buys
然后我们可以将它加入另一个表.您还需要加入它两次,这是另一个可能导致查询效率降低的问题.不幸的是,MySQL还没有CTE,因此必须复制派生的表代码.但是你至少可以从这个代码开始.
测试时间为SQLfiddle：
SELECT
buys_d.userid,
buys_d.sele_descendant_amount,
COUNT(*) AS level
FROM
( SELECT userid,
SUM(amount) AS sele_descendant_amount
FROM webineh_user_buys
GROUP BY userid
HAVING SUM(amount) >= 1000
) AS buys_d
JOIN
webineh_prefix_nodes_paths AS users
ON users.descendant_id = buys_d.userid
JOIN
( SELECT userid,
SUM(amount) AS sele_descendant_amount
FROM webineh_user_buys
GROUP BY userid
HAVING SUM(amount) >= 1000
) AS buys_a
ON users.ancestor_id = buys_a.userid
GROUP BY
buys_d.userid, buys_d.sele_descendant_amount ;
