企业管理软件销售_昆明网络推广哪里有_佛山全网营销推广_seo站内优化最主要的是什么

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

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

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

1. customers 表：存储客户信息。

   • customer_id (INT)：客户ID
   • name (VARCHAR)：客户姓名
   • email (VARCHAR)：客户邮箱

2. orders 表：存储订单信息。

   • order_id (INT)：订单ID
   • customer_id (INT)：客户ID
   • order_date (DATE)：订单日期
   • status (VARCHAR)：订单状态（例如：‘已完成’, ‘处理中’, ‘已取消’）

3. order_items 表：存储订单项信息。

   • order_item_id (INT)：订单项ID
   • order_id (INT)：订单ID
   • sku_id (INT)：SKU ID
   • quantity (INT)：数量
   • price (DECIMAL)：单价

4. spus 表：存储SPU信息。

   • spu_id (INT)：SPU ID
   • spu_name (VARCHAR)：SPU名称
   • category (VARCHAR)：产品类别

5. skus 表：存储SKU信息。

   • sku_id (INT)：SKU ID
   • spu_id (INT)：SPU ID
   • sku_name (VARCHAR)：SKU名称
   • color (VARCHAR)：颜色
   • size (VARCHAR)：尺寸

6. inventory 表：存储库存信息。

   • sku_id (INT)：SKU ID
   • stock (INT)：库存数量

任务目标：

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的销售趋势（按月）。

表结构

数据准备

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

-- 创建表
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)
BEGINDECLARE 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 DOSET 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 cJOIN orders o ON c.customer_id = o.customer_idJOIN 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 rnFROM orders oJOIN order_items oi ON o.order_id = oi.order_idGROUP 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 loJOIN 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 oJOIN order_items oi ON o.order_id = oi.order_idJOIN skus k ON oi.sku_id = k.sku_idJOIN 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 cJOIN orders o ON c.customer_id = o.customer_idJOIN 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 oJOIN order_items oi ON o.order_id = oi.order_idJOIN 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 cJOIN orders o ON c.customer_id = o.customer_idJOIN 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_soldFROM order_items oiJOIN skus k ON oi.sku_id = k.sku_idGROUP 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 ssJOIN 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_totalFROM orders oJOIN order_items oi ON o.order_id = oi.order_idGROUP 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 moaJOIN customers c ON moa.customer_id = c.customer_idJOIN 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 iJOIN skus k ON i.sku_id = k.sku_idLEFT JOIN order_items oi ON i.sku_id = oi.sku_idLEFT 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 cJOIN orders o ON c.customer_id = o.customer_idJOIN 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 rnFROM orders oJOIN order_items oi ON o.order_id = oi.order_idGROUP 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 foJOIN 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 oJOIN order_items oi ON o.order_id = oi.order_idJOIN skus k ON oi.sku_id = k.sku_idJOIN 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 cJOIN orders o ON c.customer_id = o.customer_idJOIN 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;

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

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

总结

通过上述查询，我们解决了多个复杂的查询需求，这些查询展示了如何在 MySQL 中使用子查询、窗口函数、条件聚合等高级特性来处理复杂的数据操作。