使用MySQL中的GROUP BY从结果中获得最小值-Java 学习之路

我有表它存储层次数据在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

我需要获取max path_length，其中ancestor_id至少有1000个购买但是在子查询中的位置有错误a.ancestor_id = ancestor_id错误代码

1054 - 'where子句'中的未知列'a.ancestor_id'

我添加SQLFidle演示 .

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

回复于 2024-04-20T20:37:36+08:00

使用MySQL中的GROUP BY从结果中获得最小值

1 回答

相关问题