订单最多的客户
- 题目如下所示
个人题解
这个 SQL 看起来简单,其实暗藏玄机!什么玄机?那就是 ----> MySQL 不能嵌套聚合函数!!!以下是我的个人思考过程( PS:这题和寻找平均工资最低的部门
类似 )
-
-- 1. 建表 CREATE TABLE 586_orders( order_number INT, customer_number INT, PRIMARY KEY(order_number) ); -- 2. 编写一个 SQL 查询,为下了 最多订单 的客户查找 customer_number. -- ps1:测试用例生成后,恰好有一个客户 比任何其他客户下了更多的订单. -- ps2:如果有多位客户订单数并列最多,找到它们所有的 customer_number. -- 注意事项:MySQL 当中聚合函数不能嵌套!!!! -- 解决办法:适当使用子查询解决该类问题 -- (1) 查找客户的订单数量 SELECT COUNT(o.customer_number) cus_number_count FROM 586_orders o GROUP BY o.customer_number; -- (2) 查找客户订单数量的最大值 SELECT MAX(or_count) FROM (SELECT COUNT(o.customer_number) or_count FROM 586_orders o GROUP BY o.customer_number) or_count_table; -- (3) 查找订单数量和最大值相等的 客户 id SELECT o1.customer_number FROM 586_orders o1 GROUP BY o1.customer_number HAVING COUNT(o1.customer_number) = ( SELECT MAX(or_count) FROM ( SELECT COUNT(o2.customer_number) or_count FROM 586_orders o2 GROUP BY o2.customer_number ) or_count_table ); -- 更快速的方法:使用 排序 和 limit 关键字查找最值 -- ps:在题目保证有且仅有一个最值的情况下(我个人上面那个SQL已经包含最值并列的情况) SELECT o.customer_number FROM 586_orders o GROUP BY o.customer_number ORDER BY COUNT(o.customer_number) DESC LIMIT 1;