【小练习】SQL_HAVING

 

HAVING

1. 有多少位销售代表需要管理超过 5 个客户？

   SELECT s.id, s.name, COUNT(*) num_accounts
   FROM accounts a
   JOIN sales_reps s
   ON s.id = a.sales_rep_id
   GROUP BY s.id, s.name
   HAVING COUNT(*) > 5
   ORDER BY num_accounts;
   

   实际上，我们可以使用 SUBQUERY 获得这一结果，如下所示。其他查询也可以使用这一逻辑，下面就不显示了。

   SELECT COUNT(*) num_reps_above5
   FROM(SELECT s.id, s.name, COUNT(*) num_accounts
        FROM accounts a
        JOIN sales_reps s
        ON s.id = a.sales_rep_id
        GROUP BY s.id, s.name
        HAVING COUNT(*) > 5
        ORDER BY num_accounts) AS Table1;
   

2. 有多少个客户具有超过 20 个订单？
    

   SELECT a.id, a.name, COUNT(*) num_orders
   FROM accounts a
   JOIN orders o
   ON a.id = o.account_id
   GROUP BY a.id, a.name
   HAVING COUNT(*) > 20
   ORDER BY num_orders;
   

3. 哪个客户的订单最多？

   SELECT a.id, a.name, COUNT(*) num_orders
   FROM accounts a
   JOIN orders o
   ON a.id = o.account_id
   GROUP BY a.id, a.name
   ORDER BY num_orders DESC
   LIMIT 1;
   

4. 有多少个客户在所有订单上消费的总额超过了 30,000 美元？

   SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
   FROM accounts a
   JOIN orders o
   ON a.id = o.account_id
   GROUP BY a.id, a.name
   HAVING SUM(o.total_amt_usd) > 30000
   ORDER BY total_spent;
   

5. 有多少个客户在所有订单上消费的总额不到 1,000 美元？

   SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
   FROM accounts a
   JOIN orders o
   ON a.id = o.account_id
   GROUP BY a.id, a.name
   HAVING SUM(o.total_amt_usd) < 1000
   ORDER BY total_spent;
   

6. 哪个客户消费的最多？

   SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
   FROM accounts a
   JOIN orders o
   ON a.id = o.account_id
   GROUP BY a.id, a.name
   ORDER BY total_spent DESC
   LIMIT 1;
   

7. 哪个客户消费的最少？

   SELECT a.id, a.name, SUM(o.total_amt_usd) total_spent
   FROM accounts a
   JOIN orders o
   ON a.id = o.account_id
   GROUP BY a.id, a.name
   ORDER BY total_spent
   LIMIT 1;
   

8. 哪个客户使用 facebook 作为与消费者沟通的渠道超过 6 次？

   SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
   FROM accounts a
   JOIN web_events w
   ON a.id = w.account_id
   GROUP BY a.id, a.name, w.channel
   HAVING COUNT(*) > 6 AND w.channel = 'facebook'
   ORDER BY use_of_channel;                                                                                    下面这种写法也能得到一样的结果：                                                                               SELECT a.id, a.name, COUNT(*) num_channel
   FROM accounts a
   JOIN web_events w
   ON a.id = w.account_id
   WHERE w.channel = 'facebook'
   GROUP BY a.id, a.name
   HAVING COUNT(*) > 6
   ORDER BY num_channel;

9. 哪个客户使用 facebook 作为沟通渠道的次数最多？

   SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
   FROM accounts a
   JOIN web_events w
   ON a.id = w.account_id
   WHERE w.channel = 'facebook'
   GROUP BY a.id, a.name, w.channel
   ORDER BY use_of_channel DESC
   LIMIT 1;
   

10. 哪个渠道是客户最常用的渠道？

    SELECT a.id, a.name, w.channel, COUNT(*) use_of_channel
    FROM accounts a
    JOIN web_events w
    ON a.id = w.account_id
    GROUP BY a.id, a.name, w.channel
    ORDER BY use_of_channel DESC
    LIMIT 10;
    

    上面的所有 10 列都是 direct。？？为什么还要对a.id, a.name进行聚合？？