查询订单_牛客题霸

日常在处理订单的时候我们会遇到一些问题。假设现在有2个表，订单orders表和用户customers表。其中orders的表结构如下 order_id customer_id order_date 1 101 2022-07-01 10:00:00 2 102 2022-07-02 11:30:00 3 101 2022-07-03 15:45:00 4 103 2022-07-04 09:20:00 5 102 2022-07-05 14:10:00 customers的表结构如下表格示例 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 aaa 102 bbb 103 ccc 现在你需要输出每个用户最后一次下单的信息，包括：order_id，customer_name，最近下单日期，按照customer_name升序排序。上面的例子输出如下订单表格 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 aaa 2022-07-03 15:45:00 5 bbb 2022-07-05 14:10:00 4 ccc 2022-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, '2022-07-01 10:00:00'),
  (2, 102, '2022-07-02 11:30:00'),
  (3, 101, '2022-07-03 15:45:00'),
  (4, 103, '2022-07-04 09:20:00'),
  (5, 102, '2022-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, 'aaa'),
  (102, 'bbb'),
  (103, 'ccc');

输出

order_id|customer_name|order_date
3|aaa|2022-07-03 15:45:00
5|bbb|2022-07-05 14:10:00
4|ccc|2022-07-04 09:20:00