mysql - 一种更好的方法来吸引下订单数量最多和最少的客户

Question

我的问题是找出所有下了最少和最多订单的客户。

我能想到的是:

select customer_id , count(order_id) as num 
from bab_customer right outer join bab_order_details using(customer_id)
group by customer_id
having count(order_id) >= 
    all(select count(order_id) 
        from bab_customer right outer join bab_order_details using(customer_id)
        group by customer_id)
or count(order_id) <= 
    all(select count(order_id) 
        from bab_customer right outer join bab_order_details using(customer_id)
        group by customer_id);

它给了我正确的输出，但我必须做同样的连接 3 次。有更好的方法吗？

Answer 1

这样更好吗？我不知道……

SELECT x.* 
  FROM 
     ( SELECT customer_id
            , COUNT(*) cnt 
         FROM orders 
        GROUP  
           BY customer_id
     ) x 
  JOIN 
     ( SELECT MIN(cnt) min_cnt
            , MAX(cnt) max_cnt 
         FROM 
            ( SELECT customer_id
                   , COUNT(*) cnt 
                FROM orders 
               GROUP 
                  BY customer_id
            ) n
     ) y 
    ON y.min_cnt = x.cnt 
    OR y.max_cnt = x.cnt;