MySQL常用订单表复杂查询15例

2024-11-14 08:03:27

MySQL常用订单表复杂查询15例

在电子商务平台中，我们通常需要处理大量的数据查询任务。本文将介绍十五个复杂的 MySQL 查询任务及其示例代码。需要mysql8.0.X以上环境进行测试，因为使用到了窗口函数。

假设我们有一个电子商务平台，包含以下六个表：

customers 表：存储客户信息。
- customer_id (INT)：客户ID
- name (VARCHAR)：客户姓名
- email (VARCHAR)：客户邮箱
orders 表：存储订单信息。
- order_id (INT)：订单ID
- customer_id (INT)：客户ID
- order_date (DATE)：订单日期
- status (VARCHAR)：订单状态（例如：‘已完成’, ‘处理中’, ‘已取消’）
order_items 表：存储订单项信息。
- order_item_id (INT)：订单项ID
- order_id (INT)：订单ID
- sku_id (INT)：SKU ID
- quantity (INT)：数量
- price (DECIMAL)：单价
spus 表：存储SPU信息。
- spu_id (INT)：SPU ID
- spu_name (VARCHAR)：SPU名称
- category (VARCHAR)：产品类别
skus 表：存储SKU信息。
- sku_id (INT)：SKU ID
- spu_id (INT)：SPU ID
- sku_name (VARCHAR)：SKU名称
- color (VARCHAR)：颜色
- size (VARCHAR)：尺寸
inventory 表：存储库存信息。
- sku_id (INT)：SKU ID
- stock (INT)：库存数量

任务目标：

找出每个客户的总消费金额，并按总消费金额从高到低排序。
列出每个客户的最新订单日期及其详细信息（包括订单ID、订单日期、订单状态、订单总金额）。
统计每个SPU在每个月的销售量和销售额，并按月份和SPU ID排序。
找出每个客户在每个月的订单数量和总金额，并按月份和客户ID排序。
找出在过去一年内没有下过订单的客户信息。
列出每个SKU在每个月的销售量和销售额，并按月份和SKU ID排序。
找出每个客户的平均订单金额。
列出每个SPU的最畅销SKU及其销售量。
找出每个客户的最大单笔订单金额及其订单信息。
列出每个SKU的库存情况及其最近一次销售日期。
找出每个客户的订单总数和平均订单金额。
列出每个客户的第一个订单日期及其详细信息。
统计每个SPU类别的销售量和销售额。
找出每个客户在过去三个月内的订单数量和总金额。
列出每个SKU的销售趋势（按月）。

表结构

数据准备

首先，我们需要创建表并插入一些示例数据：

-- 创建表
CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20)
);

CREATE TABLE order_items (
  order_item_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  sku_id INT,
  quantity INT,
  price DECIMAL(10, 2)
);

CREATE TABLE spus (
  spu_id INT AUTO_INCREMENT PRIMARY KEY,
  spu_name VARCHAR(100),
  category VARCHAR(50)
);

CREATE TABLE skus (
  sku_id INT AUTO_INCREMENT PRIMARY KEY,
  spu_id INT,
  sku_name VARCHAR(100),
  color VARCHAR(50),
  size VARCHAR(10)
);

CREATE TABLE inventory (
  sku_id INT PRIMARY KEY,
  stock INT
);

-- 插入示例数据
	INSERT INTO customers (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com'),
('赵六', 'zhaoliu@example.com'),
('孙七', 'sunqi@example.com'),
('周八', 'zhouba@example.com'),
('吴九', 'wujiu@example.com'),
('郑十', 'zhengshi@example.com'),
('十一', 'shiyi@example.com');


INSERT INTO spus (spu_name, category) VALUES
('iPhone 12', '电子产品'),
('三星 Galaxy S21', '电子产品'),
('耐克 Air Max', '鞋类'),
('华为 Mate 40', '电子产品'),
('阿迪达斯 Ultraboost', '鞋类'),
('小米 11', '电子产品'),
('彪马 RS-X', '鞋类'),
('OPPO Reno 5', '电子产品');

INSERT INTO skus (spu_id, sku_name, color, size) VALUES
(1, 'iPhone 12 - 64GB 黑色', '黑色', '64GB'),
(1, 'iPhone 12 - 128GB 白色', '白色', '128GB'),
(2, '三星 Galaxy S21 - 红色', '红色', '128GB'),
(2, '三星 Galaxy S21 - 蓝色', '蓝色', '256GB'),
(3, '耐克 Air Max - 红色', '红色', '9'),
(3, '耐克 Air Max - 蓝色', '蓝色', '10'),
(4, '华为 Mate 40 - 黑色', '黑色', '128GB'),
(4, '华为 Mate 40 - 白色', '白色', '256GB'),
(5, '阿迪达斯 Ultraboost - 黑色', '黑色', '9'),
(5, '阿迪达斯 Ultraboost - 白色', '白色', '10'),
(6, '小米 11 - 黑色', '黑色', '128GB'),
(6, '小米 11 - 白色', '白色', '256GB'),
(7, '彪马 RS-X - 黑色', '黑色', '9'),
(7, '彪马 RS-X - 白色', '白色', '10'),
(8, 'OPPO Reno 5 - 黑色', '黑色', '128GB'),
(8, 'OPPO Reno 5 - 白色', '白色', '256GB');

INSERT INTO inventory (sku_id, stock) VALUES
(1, 100),
(2, 50),
(3, 75),
(4, 25),
(5, 30),
(6, 40),
(7, 60),
(8, 80),
(9, 50),
(10, 70),
(11, 90),
(12, 100),
(13, 45),
(14, 55),
(15, 65),
(16, 75);

-- 插入订单和订单项数据
DELIMITER ;;
CREATE PROCEDURE GenerateOrdersAndItems(IN num_orders INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE rand_customer_id INT;
  DECLARE rand_sku_id INT;
  DECLARE rand_quantity INT;
  DECLARE rand_price DECIMAL(10, 2);
  DECLARE rand_order_date DATE;

  WHILE i < num_orders DO
    SET rand_customer_id = FLOOR(1 + RAND() * 8);
    SET rand_sku_id = FLOOR(1 + RAND() * 16);
    SET rand_quantity = FLOOR(1 + RAND() * 5);
    SET rand_price = (RAND() * 1000) + 100;
    SET rand_order_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY);

    INSERT INTO orders (customer_id, order_date, status) VALUES (rand_customer_id, rand_order_date, '已完成');
    SET @last_order_id = LAST_INSERT_ID();

    INSERT INTO order_items (order_id, sku_id, quantity, price) VALUES (@last_order_id, rand_sku_id, rand_quantity, rand_price);

    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;

CALL GenerateOrdersAndItems(50);

复杂查询任务及示例代码

1. 找出每个客户的总消费金额，并按总消费金额从高到低排序

SELECT 
  c.customer_id,
  c.name,
  SUM(oi.quantity * oi.price) AS total_spent
FROM 
  customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY 
  c.customer_id, c.name
ORDER BY 
  total_spent DESC;

2. 列出每个客户的最新订单日期及其详细信息（包括订单ID、订单日期、订单状态、订单总金额）

WITH LatestOrders AS (
  SELECT 
    o.customer_id,
    o.order_id,
    o.order_date,
    o.status,
    SUM(oi.quantity * oi.price) AS order_total,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
  FROM 
    orders o
    JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY 
    o.customer_id, o.order_id, o.order_date, o.status
)
SELECT 
  lo.customer_id,
  c.name,
  lo.order_id,
  lo.order_date,
  lo.status,
  lo.order_total
FROM 
  LatestOrders lo
  JOIN customers c ON lo.customer_id = c.customer_id
WHERE 
  lo.rn = 1;

3. 统计每个SPU在每个月的销售量和销售额，并按月份和SPU ID排序

SELECT 
  YEAR(o.order_date) AS order_year,
  MONTH(o.order_date) AS order_month,
  s.spu_id,
  s.spu_name,
  SUM(oi.quantity) AS total_quantity_sold,
  SUM(oi.quantity * oi.price) AS total_sales
FROM 
  orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN skus k ON oi.sku_id = k.sku_id
  JOIN spus s ON k.spu_id = s.spu_id
GROUP BY 
  YEAR(o.order_date), MONTH(o.order_date), s.spu_id, s.spu_name
ORDER BY 
  order_year, order_month, s.spu_id;

4. 找出每个客户在每个月的订单数量和总金额，并按月份和客户ID排序

SELECT 
  c.customer_id,
  c.name,
  YEAR(o.order_date) AS order_year,
  MONTH(o.order_date) AS order_month,
  COUNT(o.order_id) AS order_count,
  SUM(oi.quantity * oi.price) AS total_amount
FROM 
  customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY 
  c.customer_id, c.name, YEAR(o.order_date), MONTH(o.order_date)
ORDER BY 
  order_year, order_month, c.customer_id;

5. 找出在过去一年内没有下过订单的客户信息

SELECT 
  c.customer_id,
  c.name
FROM 
  customers c
LEFT JOIN 
  orders o ON c.customer_id = o.customer_id AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
WHERE 
  o.order_id IS NULL;

6. 列出每个SKU在每个月的销售量和销售额，并按月份和SKU ID排序

SELECT 
  YEAR(o.order_date) AS order_year,
  MONTH(o.order_date) AS order_month,
  k.sku_id,
  k.sku_name,
  SUM(oi.quantity) AS total_quantity_sold,
  SUM(oi.quantity * oi.price) AS total_sales
FROM 
  orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN skus k ON oi.sku_id = k.sku_id
GROUP BY 
  YEAR(o.order_date), MONTH(o.order_date), k.sku_id, k.sku_name
ORDER BY 
  order_year, order_month, k.sku_id;

7. 找出每个客户的平均订单金额

SELECT 
  c.customer_id,
  c.name,
  AVG(oi.quantity * oi.price) AS average_order_amount
FROM 
  customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY 
  c.customer_id, c.name;

8. 列出每个SPU的最畅销SKU及其销售量

WITH SkuSales AS (
  SELECT 
    k.spu_id,
    k.sku_id,
    k.sku_name,
    SUM(oi.quantity) AS total_quantity_sold
  FROM 
    order_items oi
    JOIN skus k ON oi.sku_id = k.sku_id
  GROUP BY 
    k.spu_id, k.sku_id, k.sku_name
)
SELECT 
  ss.spu_id,
  s.spu_name,
  ss.sku_id,
  ss.sku_name,
  ss.total_quantity_sold
FROM 
  SkuSales ss
  JOIN spus s ON ss.spu_id = s.spu_id
WHERE 
  ss.total_quantity_sold = (
    SELECT MAX(total_quantity_sold) 
    FROM SkuSales 
    WHERE spu_id = ss.spu_id
  );

9. 找出每个客户的最大单笔订单金额及其订单信息

WITH MaxOrderAmount AS (
  SELECT 
    o.customer_id,
    o.order_id,
    SUM(oi.quantity * oi.price) AS order_total
  FROM 
    orders o
    JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY 
    o.customer_id, o.order_id
)
SELECT 
  moa.customer_id,
  c.name,
  moa.order_id,
  o.order_date,
  o.status,
  moa.order_total
FROM 
  MaxOrderAmount moa
  JOIN customers c ON moa.customer_id = c.customer_id
  JOIN orders o ON moa.order_id = o.order_id
WHERE 
  moa.order_total = (
    SELECT MAX(order_total) 
    FROM MaxOrderAmount 
    WHERE customer_id = moa.customer_id
  );

10. 列出每个SKU的库存情况及其最近一次销售日期

SELECT 
  i.sku_id,
  k.sku_name,
  i.stock,
  MAX(o.order_date) AS last_sale_date
FROM 
  inventory i
  JOIN skus k ON i.sku_id = k.sku_id
  LEFT JOIN order_items oi ON i.sku_id = oi.sku_id
  LEFT JOIN orders o ON oi.order_id = o.order_id
GROUP BY 
  i.sku_id, k.sku_name, i.stock;

11. 找出每个客户的订单总数和平均订单金额

SELECT 
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS total_orders,
  AVG(oi.quantity * oi.price) AS average_order_amount
FROM 
  customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY 
  c.customer_id, c.name;

12. 列出每个客户的第一个订单日期及其详细信息

WITH FirstOrders AS (
  SELECT 
    o.customer_id,
    o.order_id,
    o.order_date,
    o.status,
    SUM(oi.quantity * oi.price) AS order_total,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date ASC) AS rn
  FROM 
    orders o
    JOIN order_items oi ON o.order_id = oi.order_id
  GROUP BY 
    o.customer_id, o.order_id, o.order_date, o.status
)
SELECT 
  fo.customer_id,
  c.name,
  fo.order_id,
  fo.order_date,
  fo.status,
  fo.order_total
FROM 
  FirstOrders fo
  JOIN customers c ON fo.customer_id = c.customer_id
WHERE 
  fo.rn = 1;

13. 统计每个SPU类别的销售量和销售额

SELECT 
  s.category,
  SUM(oi.quantity) AS total_quantity_sold,
  SUM(oi.quantity * oi.price) AS total_sales
FROM 
  orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN skus k ON oi.sku_id = k.sku_id
  JOIN spus s ON k.spu_id = s.spu_id
GROUP BY 
  s.category;

14. 找出每个客户在过去三个月内的订单数量和总金额

SELECT 
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS order_count,
  SUM(oi.quantity * oi.price) AS total_amount
FROM 
  customers c
  JOIN orders o ON c.customer_id = o.customer_id
  JOIN order_items oi ON o.order_id = oi.order_id
WHERE 
  o.order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
GROUP BY 
  c.customer_id, c.name;

码农公寓

MySQL常用订单表复杂查询15例

表结构

数据准备

复杂查询任务及示例代码

1. 找出每个客户的总消费金额，并按总消费金额从高到低排序

2. 列出每个客户的最新订单日期及其详细信息（包括订单ID、订单日期、订单状态、订单总金额）

3. 统计每个SPU在每个月的销售量和销售额，并按月份和SPU ID排序

4. 找出每个客户在每个月的订单数量和总金额，并按月份和客户ID排序

5. 找出在过去一年内没有下过订单的客户信息

6. 列出每个SKU在每个月的销售量和销售额，并按月份和SKU ID排序

7. 找出每个客户的平均订单金额

8. 列出每个SPU的最畅销SKU及其销售量

9. 找出每个客户的最大单笔订单金额及其订单信息

10. 列出每个SKU的库存情况及其最近一次销售日期

11. 找出每个客户的订单总数和平均订单金额

12. 列出每个客户的第一个订单日期及其详细信息

13. 统计每个SPU类别的销售量和销售额

14. 找出每个客户在过去三个月内的订单数量和总金额

15. 列出每个SKU的销售趋势（按月）

相关文章