Mysql 小测答案

数据库

https://xiaonenglife.oss-cn-hangzhou.aliyuncs.com/static/pic/2022/04/20220425180512_school.sql

1、查询订单数前十的客户名称和订单数量
2、查询没有买过牛奶的客户ID
3、查询至少买过客户ID为“VINET”所买的同一商品的其他客户ID和公司名称；
4、显示所有订单的“牛奶”、“猪肉”的数量，没有为0 
5、查询只买了一件商品的订单
6、查询销量前十的商品

问题型（可选题），
和牛奶一起购买最多的商品是什么？

1、 查询订单数前十的客户名称和订单数量

with tmp as(

select 客户ID,COUNT(*) 订单数量 from 订单 group by 客户ID
)
select a.*,b.公司名称 from tmp a join 客户 b on a.客户ID = b.客户ID order by 订单数量 desc limit 10;

2、 查询没有买过牛奶的客户ID

select 产品ID into @id from 产品 where 产品名称 = "牛奶";
with tmp as(
select 订单ID from 订单明细 where 产品ID = @id
),
tmp2 as(
select 客户ID from 订单 where 订单ID in (select 订单ID from tmp)
)
select 客户ID from 订单 where 客户ID not in (select 客户ID from tmp2)

3、 查询至少买过客户ID为“VINET”所买的同一商品的其他客户ID和公司名称；

with tmp as(
select 订单ID from 订单 where 客户ID = "VINET"
),tmp2 as(
select 产品ID from 订单明细 where 订单ID in (select 订单ID from tmp)
),tmp3 as(
select 订单ID from 订单明细 where 产品ID in (select 产品ID from tmp2) and 订单ID not in (select 
订单ID from tmp)
)
select 客户ID,公司名称 from 订单 join 客户 using(客户ID) where 订单ID in (select 订单ID from tmp3)

4、 显示所有订单的“牛奶”、“猪肉”的数量，没有为0

select 产品ID into @id1 from 产品 where 产品名称 = "牛奶";

select 产品ID into @id2 from 产品 where 产品名称 = "猪肉";

select 订单ID,if((select 数量 from 订单明细 b where b.订单ID = a.订单ID and b.产品id = @id2) is null,0,(select 数量 from 订单明细 b where b.订单ID = a.订单ID and b.产品id = @id2)) 猪肉数量,
if((select 数量 from 订单明细 b where b.订单ID = a.订单ID and b.产品id = @id1) is null,0,(select 数量 from 订单明细 b where b.订单ID = a.订单ID and b.产品id = @id1)) 牛奶数量 
from 订单 a order by 猪肉数量 desc,牛奶数量 desc;

5、 查询只买了一件商品的订单

select 订单ID,sum(数量) from 订单明细 group by 订单ID having sum(数量)=1

6、查询销量前十的商品

with tmp as(
select 产品ID,count(数量) 数量 from 订单明细 group by 产品ID order by 数量 desc limit 10
)
select * from 产品 where 产品ID in (select 产品ID from tmp)

7、和牛奶一起购买最多的商品是什么？

select 产品ID into @id from 产品 where 产品名称 = "牛奶";
with tmp as(
select 订单ID from 订单明细 where 产品ID = @id
),tmp2 as(
select * from 订单明细 where 订单ID in (select 订单ID from tmp) and 产品ID != @id
)
select 产品ID,sum(数量) 数量 from tmp2 group by 产品ID order by 数量 desc limit 1