mysql - 在 MySQL 中使用 GROUP BY 从结果中获取最小值

Question

我有一个表，它在 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 

我需要获取最大路径长度，其中祖先_id 至少有 1000 笔购买量，但在子查询中的位置出现错误，其中 a.ancestor_id =祖先_id 错误代码

1054 - Unknown column 'a.ancestor_id' in 'where clause'

我添加SQLFidle演示。

Answer 1

您可以使用此查询:

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