假设有两个表，分别是"orders"和"customers"，用于存储电商平台的订单信息和顾客信息。下面是两个表的结构和示例数据： Table: orders Orders Table table { width: 50%; border-collapse: collapse; margin: 20px auto; } th, td { border: 1px solid #000; padding: 8px; text-align: center; } th { background-color: #f2f2f2; } order_id customer_id order_date 1 101 2023-07-01 10:00:00 2 102 2023-07-02 11:30:00 3 101 2023-07-03 15:45:00 4 103 2023-07-04 09:20:00 5 102 2023-07-05 14:10:00 Table: customers Customers Table table { width: 50%; border-collapse: collapse; margin: 20px auto; } th, td { border: 1px solid #000; padding: 8px; text-align: center; } th { background-color: #f2f2f2; } customer_id customer_name 101 Alice 102 Bob 103 Carol 编写一个MySQL查询语句，查找每个顾客最后一次下单的订单信息，包括订单编号、顾客名称、以及最近下单日期。要求使用窗口函数和表联结进行解决。 Orders Table table { width: 50%; border-collapse: collapse; margin: 20px auto; } th, td { border: 1px solid #000; padding: 8px; text-align: center; } th { background-color: #f2f2f2; } order_id customer_name order_date 3 Alice 2023-07-03 15:45:00 5 Bob 2023-07-05 14:10:00 4 Carol 2023-07-04 09:20:00
示例1

输入

drop table if exists orders;
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATETIME
);

INSERT INTO orders (order_id, customer_id, order_date)
VALUES
  (1, 101, '2023-07-01 10:00:00'),
  (2, 102, '2023-07-02 11:30:00'),
  (3, 101, '2023-07-03 15:45:00'),
  (4, 103, '2023-07-04 09:20:00'),
  (5, 102, '2023-07-05 14:10:00');

drop table if exists customers;
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50)
);

INSERT INTO customers (customer_id, customer_name)
VALUES
  (101, 'Alice'),
  (102, 'Bob'),
  (103, 'Carol');

输出

order_id|customer_name|order_date
3|Alice|2023-07-03 15:45:00
5|Bob|2023-07-05 14:10:00
4|Carol|2023-07-04 09:20:00
加载中...