一、题目描述 【背景】 某精品咖啡连锁品牌在多个城市开设门店，所有门店共享统一的订单系统。运营团队需要为每家门店找出其"王牌产品"（销售额最高的产品），以及该王牌产品的"最忠实顾客"（购买该产品数量最多的顾客）。该分析需要两步关联：先定位每家门店的王牌产品，再基于该产品进一步找出该产品的头号顾客。 【表结构与字段说明】 表1：coffee_shops（咖啡门店表） shop_id：INT，门店编号，主键 shop_name：VARCHAR(50)，门店名称 city：VARCHAR(20)，所在城市 district：VARCHAR(30)，所在区域 表2：order_details（订单明细表） order_id：INT，订单编号，主键 shop_id：INT，门店编号，关联 coffee_shops.shop_id customer_name：VARCHAR(30)，顾客姓名 product_name：VARCHAR(30)，商品名称 order_date：DATE，下单日期 quantity：INT，购买数量（保证 = 1） unit_price：DECIMAL(8,2)，单价（元） 二、问题 请使用链式 LATERAL JOIN（两个 LATERAL 子查询串联，第二个 LATERAL 引用第一个 LATERAL 的输出结果）查询每家门店的王牌产品及该产品的最忠实顾客。 具体规则： （1）王牌产品：对每家门店，按商品汇总销售总额（SUM(quantity * unit_price)），取销售总额最高的1个商品。若销售总额相同，取总销量（SUM(quantity)）更高的；若仍相同，取 product_name 升序排列靠前的。 （2）最忠实顾客：在确定王牌产品后，对该门店购买过该王牌产品的所有顾客，按购买该产品的总数量（SUM(quantity)）取最多的1位顾客，同时输出该顾客首次购买该产品的日期。若总数量相同，取首次购买日期（MIN(order_date)）更早的；若仍相同，取 customer_name 升序排列靠前的。 输出以下字段：门店名称(shop_name)、所在城市(city)、王牌产品(top_product)、产品销售总额(product_revenue)、最忠实顾客(top_customer)、顾客购买数量(customer_quantity)、首次购买日期(first_purchase_date)。结果按 shop_id 升序排列。若某门店无订单记录则不出现在结果中。 三、示例数据表 coffee_shops 表： shop_id shop_name city district 1 晨光咖啡·南京西路店 上海 静安区 2 晨光咖啡·望京店 北京 朝阳区 order_details 表： order_id shop_id customer_name product_name order_date quantity unit_price 1 1 李明 冰美式 2025-03-01 2 22.00 2 1 李明 冰美式 2025-03-05 3 22.00 3 1 王芳 冰美式 2025-03-03 1 22.00 4 1 王芳 拿铁 2025-03-02 2 28.00 5 1 张伟 拿铁 2025-03-04 1 28.00 6 1 张伟 手冲瑰夏 2025-03-06 1 58.00 7 2 赵敏 燕麦拿铁 2025-03-01 3 32.00 8 2 赵敏 燕麦拿铁 2025-03-08 2 32.00 9 2 钱进 燕麦拿铁 2025-03-03 4 32.00 10 2 钱进 美式 2025-03-05 2 20.00 11 2 孙莉 美式 2025-03-02 3 20.00 12 2 孙莉 美式 2025-03-07 1 20.00 四、示例数据查询结果表 说明： 南京西路店产品销售额汇总：冰美式=(2+3+1)×22=132.00，拿铁=(2+1)×28=84.00，手冲瑰夏=1×58=58.00。王牌产品：冰美式(132.00)。 冰美式顾客购买量：李明=2+3=5，王芳=1。最忠实顾客：李明(5杯，首购03-01)。 望京店产品销售额汇总：燕麦拿铁=(3+2+4)×32=288.00，美式=(2+3+1)×20=120.00。王牌产品：燕麦拿铁(288.00)。 燕麦拿铁顾客购买量：赵敏=3+2=5，钱进=4。最忠实顾客：赵敏(5杯，首购03-01)。 shop_name city top_product product_revenue top_customer customer_quantity first_purchase_date 晨光咖啡·南京西路店 上海 冰美式 132.00 李明 5 2025-03-01 晨光咖啡·望京店 北京 燕麦拿铁 288.00 赵敏 5 2025-03-01
示例1

输入

CREATE TABLE coffee_shops (
    shop_id INT PRIMARY KEY,
    shop_name VARCHAR(50),
    city VARCHAR(20),
    district VARCHAR(30)
);

CREATE TABLE order_details (
    order_id INT PRIMARY KEY,
    shop_id INT,
    customer_name VARCHAR(30),
    product_name VARCHAR(30),
    order_date DATE,
    quantity INT,
    unit_price DECIMAL(8,2)
);

INSERT INTO coffee_shops VALUES
(1, '晨光咖啡·南京西路店', '上海', '静安区'),
(2, '晨光咖啡·望京店', '北京', '朝阳区');

INSERT INTO order_details VALUES
(1,  1, '李明', '冰美式',   '2025-03-01', 2, 22.00),
(2,  1, '李明', '冰美式',   '2025-03-05', 3, 22.00),
(3,  1, '王芳', '冰美式',   '2025-03-03', 1, 22.00),
(4,  1, '王芳', '拿铁',     '2025-03-02', 2, 28.00),
(5,  1, '张伟', '拿铁',     '2025-03-04', 1, 28.00),
(6,  1, '张伟', '手冲瑰夏', '2025-03-06', 1, 58.00),
(7,  2, '赵敏', '燕麦拿铁', '2025-03-01', 3, 32.00),
(8,  2, '赵敏', '燕麦拿铁', '2025-03-08', 2, 32.00),
(9,  2, '钱进', '燕麦拿铁', '2025-03-03', 4, 32.00),
(10, 2, '钱进', '美式',     '2025-03-05', 2, 20.00),
(11, 2, '孙莉', '美式',     '2025-03-02', 3, 20.00),
(12, 2, '孙莉', '美式',     '2025-03-07', 1, 20.00);

输出

shop_name|city|top_product|product_revenue|top_customer|customer_quantity|first_purchase_date
晨光咖啡·南京西路店|上海|冰美式|132.00|李明|5|2025-03-01
晨光咖啡·望京店|北京|燕麦拿铁|288.00|赵敏|5|2025-03-01
加载中...