列表

详情


SQL64. 对顾客ID和日期排序

描述

有Orders表
cust_id order_num order_date
andy aaaa 2021-01-01 00:00:00
andy bbbb 2021-01-01 12:00:00
bob cccc 2021-01-10 12:00:00
dick dddd 2021-01-11 00:00:00
【问题】编写 SQL 语句,从 Orders 表中检索顾客 ID(cust_id)和订单号(order_num),并先按顾客 ID 对结果进行排序,再按订单日期倒序排列。
【示例答案】
返回2列,cust_id和order_num
cust_id order_num
andy bbbb
andy aaaa
bob cccc
dick dddd
【示例解析】
首先根据cust_id进行排列,andy在bob和dick前,再根据order_date进行排列,订单号bbbb的订单时间是 "2021-01-01 12:00:00"大于订单号aaaa的时间"2021-01-01 00:00:00"

示例1

输入:

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders` (
  `cust_id` varchar(255) NOT NULL COMMENT '顾客 ID',
  `order_num` varchar(255) NOT NULL COMMENT '订单号',
  `order_date` timestamp NOT NULL COMMENT '订单时间'
);
INSERT INTO `Orders` VALUES ('andy','aaaa','2021-01-01 00:00:00'),
('andy','bbbb','2021-01-01 12:00:00'),
('bob','cccc','2021-01-10 12:00:00'),
('dick','dddd','2021-01-11 00:00:00');

输出:

andy|bbbb
andy|aaaa
bob|cccc
dick|dddd

原站题解

上次编辑到这里,代码来自缓存 点击恢复默认模板

Mysql 解法, 执行用时: 38ms, 内存消耗: 6440KB, 提交时间: 2022-03-02

select cust_id,order_num from Orders
order by cust_id ,order_date desc

Mysql 解法, 执行用时: 38ms, 内存消耗: 6444KB, 提交时间: 2022-03-06

select
cust_id,order_num
from Orders
order by cust_id asc,order_date desc

Mysql 解法, 执行用时: 38ms, 内存消耗: 6592KB, 提交时间: 2022-03-02

select cust_id, order_num
from Orders
order by cust_id, order_date desc;

Mysql 解法, 执行用时: 39ms, 内存消耗: 6416KB, 提交时间: 2022-03-03

SELECT
    cust_id,
    order_num
FROM
    Orders
ORDER BY
    cust_id,
    order_date DESC;

Mysql 解法, 执行用时: 39ms, 内存消耗: 6420KB, 提交时间: 2022-03-06

SELECT cust_id,	order_num FROM Orders
ORDER BY cust_id ASC,order_date desc