我有一个表将其存储在MySQL中的层次结构数据中，该表存储稳定的关系，但是如果删除了每个少于1000个用户的用户，并且用户级别较低，则替换为我的代码并可以正常工作，在GROUP BY之后，它包含所有具有比较关系的后代祖先然后COUNT(*) AS level计算每个用户的级别。这我有SQL代码来压缩数据，根据每个用户的最低购买量
+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 2 |
| 1 | 5 | 3 |
| 1 | 6 | 4 |
| 2 | 2 | 0 |
| 2 | 4 | 1 |
| 2 | 5 | 2 |
| 2 | 6 | 3 |
| 3 | 3 | 0 |
| 4 | 4 | 0 |
| 4 | 5 | 1 |
| 4 | 6 | 2 |
| 5 | 5 | 0 |
| 5 | 6 | 1 |
| 6 | 6 | 0 |
+-------------+---------------+-------------+
这是餐桌买的
+--------+--------+
| userid | amount |
+--------+--------+
| 2 | 2000 |
| 4 | 6000 |
| 6 | 7000 |
| 1 | 7000 |
SQL代码
SELECT a.*
FROM
( SELECT userid
FROM webineh_user_buys
GROUP BY userid
HAVING SUM(amount) >= 1000
) AS buys_d
JOIN
webineh_prefix_nodes_paths AS a
ON a.descendant_id = buys_d.userid
JOIN
(
SELECT userid
FROM webineh_user_buys
GROUP BY userid
HAVING SUM(amount) >= 1000
) AS buys_a on (a.ancestor_id = buys_a.userid )
JOIN
( SELECT descendant_id
, MAX(path_length) path_length
FROM webineh_prefix_nodes_paths
where a.ancestor_id = ancestor_id
GROUP
BY descendant_id
) b
ON b.descendant_id = a.descendant_id
AND b.path_length = a.path_length
GROUP BY a.descendant_id, a.ancestor_id
我需要获得最大path_length，其中ancestor_id至少有1000笔购买金额，但是在子查询中的哪里出错，其中a.ancestor_id = ancestor_id错误代码
1054-'where子句'中的未知列'a.ancestor_id'
我添加SQLFidle演示。
最佳答案
您可以使用以下查询：
select m.userid as descendant,
p.ancestor_id,
p.path_length
from (
select b1.userid,
min(case when b2.amount >= 1000
then p.path_length
end) as path_length
from (select userid, sum(amount) amount
from webineh_user_buys
group by userid
having sum(amount) >= 1000
) as b1
left join webineh_prefix_nodes_paths p
on p.descendant_id = b1.userid
and p.path_length > 0
left join (select userid, sum(amount) amount
from webineh_user_buys
group by userid) as b2
on p.ancestor_id = b2.userid
group by b1.userid
) as m
left join webineh_prefix_nodes_paths p
on p.descendant_id = m.userid
and p.path_length = m.path_length
order by m.userid
问题中样本数据的输出：
| userid | ancestor_id | path_length |
|--------|-------------|-------------|
| 1 | (null) | (null) |
| 2 | 1 | 1 |
| 4 | 2 | 1 |
| 6 | 4 | 2 |
SQL fiddle