SQL多表连接销售订单+产品明细+销售⽹点表
创建表
”销售订单表”记录了销售情况，每⼀张数据表⽰哪位顾客、在哪⼀天、哪个⽹点购买了什么产品，购买的数量是多少，以及对应产品的零售价
create table if not exists`销售订单表`(
`订单号`VARCHAR(50)primary key,
`顾客ID`VARCHAR(50),
`交易⽇期`date,
`交易⽹点`VARCHAR(50),
`产品`VARCHAR(50),
`销售数量`INT,
`零售价`INT
);
“产品明细表”记录了公司产品的详细信息
create table if not exists`产品明细表`(
`产品`VARCHAR(50),
`产品线`VARCHAR(50),
`产品功能`VARCHAR(50),
`零售价`INT,
CONSTRAINT FOREIGN KEY(`产品`)REFERENCES`销售订单表`(`产品`)
);
“销售⽹点表”记录了公司的销售⽹点
create table if not exists`销售⽹点表`(
`交易⽹点`VARCHAR(50),
`城市`VARCHAR(50),
`区域`VARCHAR(50),
);
插⼊
insert into销售订单表values('CS001','CustomerA','2020/1/9','StoreA','ProductA',1,100),
('CS001','CustomerA','2020/1/9','StoreA','ProductB',1,300),
('CS001','CustomerA','2020/1/9','StoreA','ProductC',1,200),
('CS002','CustomerB','2020/2/1','StoreB','ProductB',1,200),
('CS002','CustomerB','2020/2/1','StoreB','ProductC',1,300),
('CS003','CustomerC','2020/3/11','StoreA','ProductA',1,100),
('CS003','CustomerC','2020/3/11','StoreA','ProductD',1,150),
('CS003','CustomerC','2020/3/11','StoreA','ProductS',1,500),
('CS004','CustomerD','2020/3/15','StoreA','ProductB',1,300),
('CS004','CustomerD','2020/3/15','StoreA','ProductF',1,700),
('CS005','CustomerE','2020/3/16','StoreC','ProductC',1,200),
('CS006','CustomerA','2020/3/16','StoreC','ProductC',1,200),
('CS007','CustomerD','2020/4/20','StoreE','ProductA',1,100);
insert into产品明细表values('ProductA','CategoryA','FunctionA',100),
('ProductB','CategoryB','FunctionA',300),
('ProductC','CategoryC','FunctionA',200),
('ProductD','CategoryA','FunctionB',150),
('ProductF','CategoryA','FunctionC',700),
('ProductS','CategoryC','FunctionB',500);
insert into销售⽹点表values('StoreA','CityA','RegionA'),
('StoreB','CityB','RegionB'),
('StoreC','CityA','RegionA'),
('StoreD','CityC','RegionA'),
('StoreE','CityB','RegionB');
逻辑SQL
sql left join 多表连接1、分析在2020年度第⼀季度的购买⼈数，销售⾦额，客单价，客单件、⼈均购买频次
select count(distinct顾客ID)as'购买⼈数'
,sum(销售数量*零售价)as'销售⾦额'
,
sum(销售数量*零售价)/count(distinct顾客ID)as'客单价'
,sum(销售数量)/count(distinct顾客ID)as'客单件'
,count(*)/count(distinct顾客ID)as'⼈均购买频次'
where交易⽇期between'2020-1-1'and'2020-3-31'# quarter(交易⽇期)=1;
2、分析品牌在2019.5-2020.4期间的复购率
select concat(round(sum(case when co>1then1else0end)/count(*),2)*100,'%')as'复购率'
from(select顾客ID,count(*)as co
from销售订单表
where交易⽇期between'2019-5-1'and'2020-4-1'
group by顾客ID)t;
3、查既购买过ProductA⼜购买过ProductB，但没有购买ProductC的顾客⼈数，并计算平均客单价
select count(distinct顾客ID)as'顾客⼈数',
sum(销售数量*零售价)/count(distinct顾客ID)as'客单价'
from销售订单表 A
left join(select顾客ID from销售订单表where产品='ProductB') B ON A.顾客ID=B.顾客ID
left join(select顾客ID from销售订单表where产品='ProductC') C ON A.顾客ID=C.顾客ID
where A.产品='ProductA'
and b.顾客ID is not NULL
and c.顾客ID is null;
4、查每个城市购买⾦额排名第⼆的客⼈，列出其购买城市，姓名，购买⾦额
select城市，顾客ID,sum as'⾦额'
from(select c.城市,o.顾客ID,sum(销售数量*零售价)as sum,rank()over(partition by c.城市order by sum(销售数量*零售价))as rk from销售订单表 o
left join销售⽹点表 c on o.交易⽹点=c.交易⽹点
group by c.城市,o.顾客ID) t
where rk=2
5、计算每个城市的店铺数量及各个城市的⽣意汇总，输出包含⽆购买记录的城市
select c.城市,count(distinct o.交易⽹点)as'店铺数量',sum(o.销售数量*o.零售价)as'⾦额'
from销售订单表 o
right join销售⽹点表 c on o.交易⽹点=c.交易⽹点
group by c.城市

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。