SQL每日一练（牛客新题库）——第10天：排序检索数据

1. 检索顾客名称并且排序

题目：有表Customers，cust_id代表客户id，cust_name代表客户姓名。

建表语句：

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');
1
2
3
4
5
6

解题答案：

select cust_name
from Customers
group by cust_name
order by cust_name desc
1
2
3
4

2. 对顾客ID和日期排序

题目：

建表语句：

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');
1
2
3
4
5
6
7
8
9
10

解题答案：

select cust_id,order_num
from Orders
order by cust_id,order_date desc;
1
2
3

3. 按照数量和价格排序

题目：

建表语句：

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems` (
  `quantity` INT(64) NOT NULL COMMENT '数量',
  `item_price` INT(64) NOT NULL COMMENT '订单价格'
);
INSERT INTO `OrderItems` VALUES (1,100),
(10,1003),
(2,500);
1
2
3
4
5
6
7
8

解题答案：

select quantity,item_price
from OrderItems
order by quantity desc,item_price desc 
1
2
3

4. 检查SQL语句

题目：

建表语句：

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('海底捞'),
('小龙坎'),
('大龙燚');
1
2
3
4
5
6
7

解题答案：

SELECT vend_name
FROM Vendors 
ORDER BY vend_name DESC;
1
2
3

运行结果：