SQL获取每个用户消费额最高的商品

最新推荐文章于 2024-09-12 11:14:47 发布

原创最新推荐文章于 2024-09-12 11:14:47 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #java #mysql

面试题专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何通过SQL从订单表和销量表中获取每个用户消费额最高的商品，考虑到可能存在多个商品消费额相同的情况，需要先找出每个用户的最高消费额，再进行关联查询。

一、题目描述

给定订单表和销量表，编写SQL查询每个用户消费额最高的商品。

二、表结构

订单表

DROP TABLE IF EXISTS `sale`;
CREATE TABLE `sale`  (
  `sale_id` int NOT NULL,
  `user_id` int NULL DEFAULT NULL,
  `product_id` int NULL DEFAULT NULL,
  `num` int NULL DEFAULT NULL,
  PRIMARY KEY (`sale_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

商品表

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `product_id` int NOT NULL,
  `price` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

三、插入数据

#订单表
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (1, 1, 1, 5);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (2, 1, 2, 4);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (3, 1, 3, 2);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (4, 2, 3, 5);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (5, 2, 2, 10);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (6, 3, 1, 100);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (7, 3, 3, 20);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (8, 3, 2, 1);
INSERT INTO `sale` (`sale_id`, `user_id`, `product_id`, `num`) VALUES (9, 2, 1, 1);
# 商品表
INSERT INTO `bianchengbang_jdbc`.`product` (`product_id`, `price`) VALUES (1, 20.00);
INSERT INTO `bianchengbang_jdbc`.`product` (`product_id`, `price`) VALUES (2, 50.00);
INSERT INTO `bianchengbang_jdbc`.`product` (`product_id`, `price`) VALUES (3, 100.00);

四、具体思路

可能存在多个商品价格不同，但总消费额一样的情况

先查出每个用户最高的消费额

SELECT
    a.user_id,
    max(a.num * b.price) as maxprice
FROM
    sale a join 
    product b on a.product_id = b.product_id
GROUP BY a.user_id

执行结果

再以执行结果关联原先的表查询

SELECT
    a.user_id,
    a.product_id ,
    c.maxprice
FROM
    sale a
    JOIN product b ON a.product_id = b.product_id
    JOIN (
                SELECT
                    a.user_id,
                    max( a.num * b.price ) AS maxprice 
                FROM
                    sale a
                    JOIN product b ON a.product_id = b.product_id 
                GROUP BY
                    a.user_id 
    ) c ON a.user_id = c.user_id 
    AND a.num * b.price = c.maxprice

执行结果：