mysql多表联查--牛客刷题分享

题目

Customers 表有字段，顾客名称：cust_name、顾客id：cust_id

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders订单信息表，含有字段，订单号：order_num、顾客id：cust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

OrderItems表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

【问题】

除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

【示例结果】返回顾客名称 cust_name、订单号order_num、订单总额OrderTotal

cust_name	order_num	OrderTotal
an	a5	375
andy	a1	10000
ben	a2	2000
hex	a7	49
tom	a4	1250
tony	a3	150

【示例解析】

例如顾客名称cust_name为an的顾客的订单a5的订单总额为quantity*item_price = 15 * 25 = 375，最后以cust_name和order_num来进行升序排序。

建表语句

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');

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 `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  quantity INT(16) NOT NULL COMMENT '商品数量',
  item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

实现思路

遇到多表查询不要慌张，先分析它需要几张表的联合查询以及需要查询的字段

首先是，需要查询 cust_name,order_num,OrderTotal这三个字段；
cust_name在Customers表，order_num在Orders，而OrderTotal需要根据OrderItems表中的字段进行计算；由此得出需要三张表的查询，而第三张表有些特殊。
下面这是OrderItems表，订单总价是用quantity * item_price计算得到；

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

所以我们需要通过这张表构建出如下表，这才是能让我们顺利进行多表联查的表

如何得到它呢？

第一种实现:
如果使用这种聚合函数的，一定要用order_num分组，否则不然求和的时候，计算的结果是所有的订单总价，和第一个订单名（可以自己试下）

select
    order_num,
    sum(quantity * item_price) OrderTotal
from
    OrderItems
group by
    order_num

第二种实现

select
    order_num,
    quantity * item_price OrderTotal
from
    OrderItems

得到这三张表之后便可以开始联查啦！

一个一个来，我们先联查Orders 和Customers 表，得到如下表

select
    c.cust_name,
    o.order_num

from
    Orders o
        join Customers c on c.cust_id = o.cust_id

然后在用这个表，和我们之前查询到的那个订单总价表联查

select
  c.cust_name,
  t.order_num,
  t.OrderTotal
from
  Orders o
  join (
    select
      order_num,
      sum(quantity * item_price) OrderTotal
    from
      OrderItems
    group by
      order_num
  ) t on t.order_num = o.order_num
  join Customers c on c.cust_id = o.cust_id
order by
  c.cust_name,
  t.order_num;

ok，大功告成，再根据需要排序的条件做些完善就可以啦

---

tips:在进行sql查询时，首先忽略掉排序条件和分页条件，因为这个的难度是最小的，而且这两个是最后执行的，不需要过早考虑。

---

代码实现

select
  c.cust_name,
  t.order_num,
  t.OrderTotal
from
  Orders o
  join (
    select
      order_num,
      sum(quantity * item_price) OrderTotal
    from
      OrderItems
    group by
      order_num
  ) t on t.order_num = o.order_num
  join Customers c on c.cust_id = o.cust_id
order by
  c.cust_name,
  t.order_num;

针对以上代码，在表连接条件处；如join Customers c on c.cust_id = o.cust_id，比较的列名相等，可以使用join Customers using（cust_id )代替

还有就是计算订单总价哪里，使用到group by函数对订单进行分组，否则不然求和的时候，计算的结果是所有的订单总价，和第一个订单名
这里也可以不用sum函数，直接用quantity * item_price as OrderTotal；这样不用再用group by，也可以直接得到顶到总价和他对应的订单名