MySQL之查询练习(2)

数据准备

-- 1. 订单表 (orders)
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_price DECIMAL(10, 2) DEFAULT 0.00,
    status VARCHAR(50) DEFAULT 'Pending'
);

-- 2. 商品表 (products)
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 3. 订单详情表 (order_details)
CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

-- 初始化数据
-- 插入商品数据（电子产品类）
INSERT INTO products (name, category_id, price) VALUES
('Laptop', 1, 1200.00),
('Smartphone', 1, 800.00),
('Headphones', 1, 150.00),
('Coffee Maker', 2, 100.00),
('Blender', 2, 80.00);

-- 插入订单数据
INSERT INTO orders (customer_id, order_date, total_price, status) VALUES
(101, '2025-06-10', 0, 'Pending'),  -- 初始总价设为0，由详情表计算
(102, '2025-06-15', 0, 'Shipped'),
(103, '2025-06-20', 0, 'Delivered');

-- 插入订单详情数据（关联订单与商品）
INSERT INTO order_details (order_id, product_id, quantity) VALUES
(1, 1, 1),  -- 订单1：1台笔记本
(1, 3, 2),  -- 订单1：2副耳机
(2, 2, 1),  -- 订单2：1部手机
(3, 4, 1),  -- 订单3：1台咖啡机
(3, 5, 1);  -- 订单3：1台搅拌机

习题1

练习题

1. 查询每个类别中最畅销的产品（按销量，即订单详情中的数量总和）
   • 假设“最畅销”是指销量最高的产品，每个类别中只取一个。
   • 输出类别ID、产品名称和总销量。
2. 查询每位客户在2023年的订单总金额和订单数量
   • 输出客户ID、总金额和订单数量。
   • 如果客户在2023年没有订单，则不显示。
3. 找出销售额（total_price）最高的订单所购买的产品列表
   • 输出订单ID、产品名称和数量。
   • 假设可能有多个订单共享相同的最高销售额，但每个订单应独立列出其购买的产品。
4. 计算每个类别中产品的平均价格，并按平均价格从高到低排序
   • 输出类别ID和平均价格。
   • 如果某个类别中没有产品，则不显示该类别。
5. 找出在特定日期（如’2023-04-01’）之后每个客户首次下单的日期
   • 输出客户ID和首次下单日期。
   • 如果客户在指定日期之后没有下过单，则不显示该客户。

提示

• 对于第一题，你可能需要使用子查询或窗口函数（如ROW_NUMBER()）来确定每个类别中销量最高的产品。
• 对于第二题，使用WHERE子句来筛选日期，并使用GROUP BY和聚合函数来计算总金额和订单数量。
• 第三题可能需要你首先确定最高销售额，然后使用这个信息来查询相关订单和产品。
• 第四题涉及聚合和排序，使用AVG()函数计算平均价格，并按该值排序。
• 第五题可能需要你使用JOIN来连接orders表和customers表（虽然题目中未提及customers表，但假设存在，包含customer_id），并使用子查询或窗口函数来确定首次下单日期。

这个练习涵盖了MySQL中多个高级查询技巧，希望对你有所帮助！

答案

-- 1. 查询每个类别中最畅销的产品（按销量，即订单详情中的数量总和）
   -- 假设“最畅销”是指销量最高的产品，每个类别中只取一个。
   -- 输出类别ID、产品名称和总销量。
-- 方法1：使用子查询不会写hhh~
select * from products a 
left join order_details b on a.product_id = b.product_id;

-- 方法2 窗口函数
-- 第一步同统计同类型销量
select a.category_id,a.`name`,b.quantity,ROW_NUMBER() over(partition by a.category_id ORDER BY b.quantity desc) as rnk from products a 
left join order_details b on a.product_id = b.product_id;

-- 第二步按照销量排序
select 
	t.category_id,
	t.`name`,
	t.`总数`,
	RANK() over(PARTITION by t.category_id ORDER BY t.`总数` desc) as rnk
from 
(select a.category_id,a.`name`, sum(b.quantity) as 总数 from products a 
left join order_details b on a.product_id = b.product_id
GROUP BY a.`name`) as t;

-- 第三步结果
select 
	r.category_id,
	r.`name`,
	r.`总数`
from (
select 
	t.category_id,
	t.`name`,
	t.`总数`,
	RANK() over(PARTITION by t.category_id ORDER BY t.`总数` desc) as rnk
from 
(select a.category_id,a.`name`, sum(b.quantity) as 总数 from products a 
left join order_details b on a.product_id = b.product_id
GROUP BY a.`name`) as t) as r 
where r.rnk = 1;



-- 2. 查询每位客户在2023年的订单总金额和订单数量
   -- 输出客户ID、总金额和订单数量。
   -- 如果客户在2023年没有订单，则不显示。
select a.customer_id,sum(a.total_price), SUM(b.quantity) from orders a left join order_details b on a.order_id = b.order_id 
where year(a.order_date) = '2025'
GROUP BY a.customer_id;


-- 3. 找出销售额（total_price）最高的订单所购买的产品列表
   -- 输出订单ID、产品名称和数量。
   -- 假设可能有多个订单共享相同的最高销售额，但每个订单应独立列出其购买的产品。
select c.order_id , a.name, b.quantity ,rank() over(order by c.total_price desc) as d from products a 
left join order_details b on a.product_id = b.product_id 
left join orders c on c.order_id = b.order_id;
	 
select * from 
(select c.order_id , a.name, b.quantity ,rank() over(order by c.total_price desc) as d from products a 
left join order_details b on a.product_id = b.product_id 
left join orders c on c.order_id = b.order_id) as e
where e.d = 1;

-- 4. 计算每个类别中产品的平均价格，并按平均价格从高到低排序
   -- 输出类别ID和平均价格。
   -- 如果某个类别中没有产品，则不显示该类别。
select a.category_id,AVG(a.price) from products a 
where a.`name` !="" 
GROUP BY a.category_id 
order by AVG(a.price) desc;


-- 5. 找出在特定日期（如'2023-04-01'）之后每个客户首次下单的日期
   -- 输出客户ID和首次下单日期。
   -- 如果客户在指定日期之后没有下过单，则不显示该客户。
-- 先排序
select 
	a.customer_id,
	a.order_date, 
	ROW_NUMBER() over(PARTITION by a.customer_id ORDER BY a.order_date asc) as rnk
from orders a 
where a.order_date >= "2024-08-01";

-- 结果如下
select t.customer_id,t.order_date from 
(select 
	a.customer_id,
	a.order_date, 
	ROW_NUMBER() over(PARTITION by a.customer_id ORDER BY a.order_date asc) as rnk
from orders a 
where a.order_date >= "2024-08-01") as t
where t.rnk = 1;

练习2

对于MySQL数据横向展示的练习题，我们通常指的是将原本可能通过多行展示的数据，通过某种方式（如使用条件聚合或PIVOT操作，尽管MySQL原生不直接支持PIVOT语法）转换为列的形式来展示。这种转换在处理报表或数据分析时非常有用。

下面是一个基于您之前提供的表结构（orders, products, order_details）的MySQL数据横向展示练习题：

题目背景

假设我们想要分析某个特定订单（比如order_id = 1）中购买的所有产品的数量，但我们希望将这些产品的数量以列的形式展示出来，而不是作为多行。

示例数据

-- orders 表  
INSERT INTO orders (order_id, customer_id, order_date, total_price, status)  
VALUES (1, 101, '2023-04-01', 150.00, 'shipped');  
  
-- products 表  
INSERT INTO products (product_id, name, category_id, price)  
VALUES (1, 'Product A', 1, 50.00),  
       (2, 'Product B', 2, 75.00),  
       (3, 'Product C', 1, 25.00);  
  
-- order_details 表  
INSERT INTO order_details (order_detail_id, order_id, product_id, quantity)  
VALUES (1, 1, 1, 2),  
       (2, 1, 2, 1),  
       (3, 1, 3, 1);

练习题

查询订单ID为1的订单中，每个产品的名称和数量，并将这些数量以列的形式展示。

由于MySQL不直接支持PIVOT操作，我们需要使用条件聚合（CASE语句结合SUM或MAX等聚合函数）来实现这一需求。

SELECT  
    o.order_id,  
    MAX(CASE WHEN p.name = 'Product A' THEN od.quantity ELSE 0 END) AS 'Product A Quantity',  
    MAX(CASE WHEN p.name = 'Product B' THEN od.quantity ELSE 0 END) AS 'Product B Quantity',  
    MAX(CASE WHEN p.name = 'Product C' THEN od.quantity ELSE 0 END) AS 'Product C Quantity'  
FROM  
    orders o  
JOIN  
    order_details od ON o.order_id = od.order_id  
JOIN  
    products p ON od.product_id = p.product_id  
WHERE  
    o.order_id = 1  
GROUP BY  
    o.order_id;

注意：这个查询假设你已经知道要查询哪些产品的数量，并且这些产品的名称是硬编码在CASE语句中的。在实际应用中，如果产品种类很多或经常变化，这种方法可能不太灵活。

如果你想要一个更动态的方法，即不硬编码产品名称，那么你可能需要使用存储过程或应用程序逻辑来动态构建这个查询，因为MySQL本身不直接支持动态列名的查询。

额外挑战

• 尝试编写一个存储过程，该过程可以接收订单ID作为参数，并动态地生成上述查询，以适应不同的产品集合。
• 思考如何在没有硬编码产品名称的情况下，将任意数量的产品数量以列的形式展示出来。这通常涉及到在应用程序层面进行更复杂的处理。

练习 获得名词，横向排列

create table tb(
   `Name` varchar(10) ,
   `Subject` varchar(10) ,
   Result  int(11)
);

insert into tb(Name , Subject , Result) values('张三' , '语文' , 74);
insert into tb(Name , Subject , Result) values('张三' , '数学' , 83);
insert into tb(Name , Subject , Result) values('张三' , '物理' , 93);
insert into tb(Name , Subject , Result) values('李四' , '语文' , 74);
insert into tb(Name , Subject , Result) values('李四' , '数学' , 84);
insert into tb(Name , Subject , Result) values('李四' , '物理' , 94);

-- 获得名次
-- select tb from tb a left join tb b on 
select tb.*,ROW_NUMBER() over (order by tb.Result desc) as rnk from tb;

-- 横向排列
SELECT 
    Name AS '姓名',
    SUm(CASE WHEN Subject = '语文' THEN Result END) AS '语文',
    MAX(CASE WHEN Subject = '数学' THEN Result END) AS '数学',
    MAX(CASE WHEN Subject = '物理' THEN Result END) AS '物理'
FROM tb
GROUP BY Name;