【面试题】某公司数据库里有3张表,销售订单表、产品明细表、销售网点表
”销售订单表”记录了销售情况,每一张数据表示哪位顾客、在哪一天、哪个网点购买了什么产品,购买的数量是多少,以及对应产品的零售价
销售订单表、产品明细表、销售网点表字段之间的关系如下
销售订单表和产品明细表通过“产品”字段关联,销售订单表和销售网点通过“交易网点”关联
【问题】查找既购买过ProductA又购买过ProductB,但没有购买ProductC的用户人数,并计算平均客单价
【解题思路】
需要把满足三个条件的用户查找出来:
1)买过ProductA
2)买过ProductB
3)没有购买ProductC
select 顾客ID,销售数量,零售价,订单号
from 销售订单表
where
顾客ID in (select 顾客ID from 销售订单表 where 产品 ='ProductA')
and
顾客ID in (select 顾客ID from 销售订单表 where 产品 ='ProductB')
and
顾客ID not in (select 顾客ID from 销售订单表 where 产品 ='ProductC');
我们把上一步的查询结果记录为临时表a。然后,再用聚合函数计算顾客人数,平均客单价
select
count(distinct(a.顾客ID))as 顾客人数,
sum(a.销售数量*a.零售价)/count(distinct(a.订单号)) as 客单价
from a;
我临时表a的SQL带入上面SQL里,就是最终的SQL:
select
count(distinct(a.顾客ID))as 顾客人数,
sum(a.销售数量*a.零售价)/count(distinct(a.订单号)) as 客单价
from
(select 顾客ID,销售数量,零售价,订单号
from 销售订单表
where
顾客ID in (select 顾客ID from 销售订单表 where 产品 ='ProductA')
and
顾客ID in (select 顾客ID from 销售订单表 where 产品 ='ProductB')
and
顾客ID not in (select 顾客ID from 销售订单表 where 产品 ='ProductC')
) as a;
【本题考点】
1.将多条件转化为SQL的能力
2.in的用法,SQL中in常用于where表达式中,其作用是查询某个范围内的数据
如果字段范围比较多,可以使用and或or运算符。如果要排除、否定某个范围前面加上not,为not in
3.聚合函数的使用