分组并计算各组的聚合值（COUNT、SUM）

-- 创建user_info表
CREATE TABLE `user_info`
(
    `user_id` int(11) unsigned NOT NULL COMMENT '用户ID',
    `name`    varchar(64) NOT NULL COMMENT '用户姓名',
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 创建order_info表
CREATE TABLE `order_info`
(
    `order_id` int(11) unsigned NOT NULL COMMENT '订单ID',
    `user_id`  int(11) unsigned DEFAULT NULL COMMENT '用户ID',
    `goods`    varchar(255)   DEFAULT NULL COMMENT '商品名称',
    `amount`   decimal(10, 2) DEFAULT NULL COMMENT '订单金额',
    PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 插入测试数据
INSERT INTO `user_info` VALUES (1001, '刘一');
INSERT INTO `user_info` VALUES (1002, '陈二');
INSERT INTO `user_info` VALUES (1003, '张三');
INSERT INTO `user_info` VALUES (1004, '李四');
INSERT INTO `user_info` VALUES (1005, '王五');
INSERT INTO `user_info` VALUES (1006, '赵六');
INSERT INTO `user_info` VALUES (1007, '孙七');
INSERT INTO `user_info` VALUES (1008, '周八');
INSERT INTO `user_info` VALUES (1009, '吴九');
INSERT INTO `user_info` VALUES (1010, '郑十');


-- 插入测试数据
INSERT INTO `order_info` VALUES (80001, 1004, '雷碧碳酸饮料', 35.25);
INSERT INTO `order_info` VALUES (80002, 1003, '脉劫电解质运动饮料', 5.26);
INSERT INTO `order_info` VALUES (80003, 1003, '康帅博红烧牛肉面', 10.24);
INSERT INTO `order_info` VALUES (80004, 1008, '蓝月壳洗衣粉', 8.88);
INSERT INTO `order_info` VALUES (80005, 1003, '营养专线果汁牛奶饮品', 4.13);
INSERT INTO `order_info` VALUES (80006, 1004, '老干爹拌面酱', 2.50);


-- 查询下单数量最多的前五名用户
SELECT `user_info`.`user_id`,
       `user_info`.`name`,
       COUNT(`order_info`.`order_id`) AS `order_count`
FROM `user_info`
         LEFT JOIN `order_info` ON `user_info`.`user_id` = `order_info`.`user_id`
GROUP BY `user_info`.`user_id`
ORDER BY `order_count` DESC LIMIT 5;


-- 查询订单金额总和最高的前五名用户
SELECT `user_info`.`user_id`,
       `user_info`.`name`,
       SUM(`order_info`.`amount`) AS `amount_sum`
FROM `user_info`
         LEFT JOIN `order_info` ON `user_info`.`user_id` = `order_info`.`user_id`
GROUP BY `user_info`.`user_id`
ORDER BY `amount_sum` DESC LIMIT 5;


-- 查询从来没有下过单的用户
SELECT `user_info`.`user_id`,
       `user_info`.`name`
FROM `user_info`
         LEFT JOIN `order_info` ON `user_info`.`user_id` = `order_info`.`user_id`
WHERE `order_info`.`order_id` IS NULL;