• 牛客SQL必会知识

    文章目录

    1. SQL91 返回购买价格为 10 美元或以上产品的顾客列表

    描述

    OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num

    OrderItems表

    order_numitem_price
    a110
    a21
    a21
    a42
    a55
    a21
    a77

    Orders表

    order_numcust_id
    a1cust10
    a2cust1
    a2cust1
    a4cust2
    a5cust5
    a2cust1
    a7cust7

    【问题】使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
    注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

    【示例结果】返回顾客id cust_id

    cust_id
    cust10

    【示例解析】

    cust10顾客下单的订单为a1，a1的售出价格大于等于10

    示例1

    输入：

    DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    item_price INT(16) NOT NULL COMMENT '售出价格'
  );
  INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
  );
  INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');

    输出：

    cust10
    解法1 - 内联
    select cust_id
from OrderItems oi
inner join Orders o
on oi.order_num = o.order_num
where item_price >= 10
    解法2 - 子查询
    select cust_id
from Orders
where order_num in
(
    select order_num
    from OrderItems
    where item_price >= 10
)

    • 数据量大的情况下，使用连接查询效率更高，因为子查询相当于for循环，要执行多次子查询，而连接只需要查询一次;

    • 数据量小的情况下，子查询更容易控制和操作。

    2. SQL92 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

    描述

    表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date

    OrderItems表

    prod_idorder_num
    BR01a0001
    BR01a0002
    BR02a0003
    BR02a0013

    Orders表

    order_numcust_idorder_date
    a0001cust102022-01-01 00:00:00
    a0002cust12022-01-01 00:01:00
    a0003cust12022-01-02 00:00:00
    a0013cust22022-01-01 00:20:00

    【问题】

    编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 “BR01” 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

    【示例结果】返回顾客id cust_id和定单日期order_date。

    cust_idorder_date
    cust102022-01-01 00:00:00
    cust12022-01-01 00:01:00

    【示例解析】

    产品id为"BR01"的订单a0001和a002的下单顾客cust10和cust1的下单时间分别为2022-01-01 00:00:00和2022-01-01 00:01:00

    示例1

    输入：

    DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

    复制

    输出：

    cust10|2022-01-01 00:00:00
cust1|2022-01-01 00:01:00
    解法1 - 内联
    select cust_id, order_date
from OrderItems oi
inner join
Orders o
on oi.order_num = o.order_num
where prod_id = 'BR01'
order by order_date
    解法2 - 子查询
    select cust_id, order_date
from Orders
where order_num in
(
    select order_num
    from OrderItems
    where prod_id = 'BR01'
)
order by order_date

    3. SQL93 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

    描述

    你想知道订购 BR01 产品的日期，有表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；Customers表含有cust_email 顾客邮件和cust_id顾客id

    OrderItems表

    prod_idorder_num
    BR01a0001
    BR01a0002
    BR02a0003
    BR02a0013

    Orders表

    order_numcust_idorder_date
    a0001cust102022-01-01 00:00:00
    a0002cust12022-01-01 00:01:00
    a0003cust12022-01-02 00:00:00
    a0013cust22022-01-01 00:20:00

    Customers表代表顾客信息，cust_id为顾客id，cust_email为顾客email

    cust_idcust_email
    cust10cust10@cust.com
    cust1cust1@cust.com
    cust2cust2@cust.com

    【问题】返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

    提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

    【示例结果】

    返回顾客email cust_email

    cust_email
    cust10@cust.com
    cust1@cust.com

    `【示例解析】

    产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的顾客email cust_email分别是：cust10@cust.com 、cust1@cust.com

    示例1

    输入：

    DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');

    输出：

    cust10@cust.com
cust1@cust.com
    解法1
    select cust_email
from OrderItems oi
inner join Orders o
inner join Customers c
on oi.order_num = o.order_num and
o.cust_id = c.cust_id
where prod_id = 'BR01'
    解法2
    select cust_email
from Customers
where cust_id in
(
    select cust_id
    from Orders
    where order_num in
    (
        select order_num
        from OrderItems
        where prod_id = 'BR01'
    )
)

    4. SQL94 返回每个顾客不同订单的总金额

    描述

    我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

    OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。

    order_numitem_pricequantity
    a000110105
    a000211100
    a00021200
    a001321121
    a0003510
    a0003119
    a000375

    Orders表订单号：order_num、顾客id：cust_id

    order_numcust_id
    a0001cust10
    a0002cust1
    a0003cust1
    a0013cust2

    【问题】

    编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

    提示：你之前已经使用 SUM()计算订单总数。

    【示例结果】返回顾客id cust_id和total_order下单总额

    cust_idtotal_ordered
    cust22242
    cust11300
    cust101050
    cust2104

    【示例解析】cust2在Orders里面的订单a0013，a0013的售出价格是2售出数量是1121，总额是2242，最后返回cust2的支付总额是2242。

    示例1

    输入：

    DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	item_price INT(16) NOT NULL COMMENT '售出价格',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');

    输出：

    cust2|2242.000
cust10|1050.000
cust1|104.000
    解法
    select cust_id, sum(item_price * quantity) as total_ordered
from OrderItems oi
inner join Orders o
on oi.order_num = o.order_num
group by cust_id
order by sum(item_price * quantity) desc

    5. SQL100 确定最佳顾客的另一种方式（二）

    描述

    OrderItems表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems表有订单号order_num和item_price商品售出价格、quantity商品数量

    order_numitem_pricequantity
    a110105
    a211100
    a21200
    a421121
    a5510
    a2119
    a775

    Orders表含有字段order_num 订单号、cust_id顾客id

    order_numcust_id
    a1cust10
    a2cust1
    a3cust2
    a4cust22
    a5cust221
    a7cust2217

    顾客表Customers有字段cust_id 客户id、cust_name 客户姓名

    cust_idcust_name
    cust10andy
    cust1ben
    cust2tony
    cust22tom
    cust221an
    cust2217hex

    【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

    提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

    【示例结果】

    cust_nametotal_price
    andy1050
    ben1319
    tom2242

    【示例解析】

    总额（item_price 乘以 quantity）大于等于1000的订单号，例如a2对应的顾客id为cust1，cust1的顾客名称cust_name是ben，最后返回ben作为order_num a2的quantity * item_price总和的结果1319。

    示例1

    输入：

    DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	item_price INT(16) NOT NULL COMMENT '售出价格',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);


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 ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

    输出：

    andy|1050.000
ben|1319.000
tom|2242.000
    解法
    select cust_name, sum(item_price * quantity) as total_price
from OrderItems oi
inner join Orders o
inner join Customers c
on oi.order_num = o.order_num
and o.cust_id = c.cust_id
group by cust_name
having sum(item_price * quantity) >= 1000
order by total_price

    6. SQL108 组合 Products 表中的产品名称和 Customers 表中的顾客名称

    描述

    Products表含有字段prod_name代表产品名称

    prod_name
    flower
    rice
    ring
    umbrella

    Customers表代表顾客信息，cust_name代表顾客名称

    cust_name
    andy
    ben
    tony
    tom
    an
    lee
    hex

    【问题】

    编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。

    【示例结果】

    prod_name
    an
    andy
    ben
    flower
    hex
    lee
    rice
    ring
    tom
    tony
    umbrella

    【示例解析】

    拼接cust_name和prod_name并根据结果升序排序

    示例1

    输入：

    DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('flower'),
('rice'),
('ring'),
('umbrella');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('andy'),('ben'),('tony'),('tom'),('an'),('lee'),('hex');

    输出：

    an
andy
ben
flower
hex
lee
rice
ring
tom
tony
umbrella
    解法
    select prod_name
from Products
union
select cust_name as prod_name
from Customers
order by prod_name
  • 相关阅读:
    Java项目：SSM在线宿舍管理系统
    基于ssm的固定收益营销系统设计与实现（ERP系统）
    排序：归并（Merge）排序算法分析
    sso单点登录的实现。
    Python 代码混淆工具概述
    Dcoker入门，小白也学得懂！
    NM01-通用网络管理接口模块功能概述与API定义
    tcpdump进行DNS抓包
    避坑之路 —— 前后端 json 的注意问题
    JavaScript switch语句
  • 原文地址：https://blog.csdn.net/F15217283411/article/details/126956288