一道经典的sql面试题

时间:2021-08-20 02:59:27

今天讲解一道经典的sql面试题:
假设我们的数据是这个样子的:假设我们的表名称:sales

姓名(name) 日期(day) 每日销售金额(sal)
张三 2016-01-01 200.0
张三 2016-01-03 300.0
李四 2016-01-02 200.0
李四 2016-01-04 400.0
王五 2016-01-05 110.0
王五 2016-01-06 420.0
张三 2016-02-01 110.0
张三 2016-02-03 220.0
李四 2016-02-02 120.0
李四 2016-02-04 150.0
王五 2016-02-05 220.0
王五 2016-02-06 300.0
张三 2016-03-01 220.0
张三 2016-03-03 330.0
李四 2016-03-02 290.0
李四 2016-03-04 200.0
王五 2016-03-05 150.0
王五 2016-03-06 250.0

题目:使用一条sql按照月份统计出每个人在每个月的销售金额,以及在本月份的年度累计销售金额

我们希望得到如下的结果:

姓名 月份 本月销售金额 年度累计销售金额
张三 2016-01 500.0 500.0
张三 2016-02 330.0 830.0
张三 2016-03 550.0 1380.0
李四 2016-01 600.0 600.0
李四 2016-02 270.0 870.0
李四 2016-03 490.0 1360.0
王五 2016-01 530.0 530.0
王五 2016-02 520.0 1050.0
王五 2016-03 400.0 1450.0

解题
1.截取日期到月
这里我是创建了一个视图:

create view sales_view as select name,substr(DATE_FORMAT(day,'%Y-%m-%d'),1,7) as mon,sal from sales ;

在视图:sales_view中的数据也就变成了

姓名(name) 月份(mon) 每日销售金额(sal)
张三 2016-01 200.0
张三 2016-01 300.0
李四 2016-01 200.0
李四 2016-01 400.0
王五 2016-01 110.0
王五 2016-01 420.0
张三 2016-02 110.0
张三 2016-02 220.0
李四 2016-02 120.0
李四 2016-02 150.0
王五 2016-02 220.0
王五 2016-02 300.0
张三 2016-03 220.0
张三 2016-03 330.0
李四 2016-03 290.0
李四 2016-03 200.0
王五 2016-03 150.0
王五 2016-03 250.0

2.统计每个人每个月的销售情况

排序:按照每个人,每个月
统计每月的销售总额

sql:

select name,mon,sum(sal) as sum_sal from sales_view group by name,mon;

3.自己和自己join,约束条件是name
sql:

select * from ( (select name,mon,sum(sal) as sum_sal from sales_view group by name,mon) l inner join (select name,mon,sum(sal) as sum_sal from sales_view group by name,mon) r on l.name=r.name)

根据笛卡尔积表特性:
张三 1月份 【销售额】 将会对应到N个月的张三的销售额
如图:
一道经典的sql面试题

这样我们计算累计金额的时候,就可以group by r.name,r.mon首先按照姓名排序,sum(l.sum_sal),同时添加一个where条件:l.mon<=r.mon
4.得到累计销售金额
sql:

select *,sum(l.sum_sal) from ( (select name,mon,sum(sal) as sum_sal from sales_view group by name,mon) l inner join (select name,mon,sum(sal) as sum_sal from sales_view group by name,mon) r on l.name=r.name) where l.mon<=r.mon group by r.name,r.mon

至此,你会惊奇的发现右侧的结果就是答案
一道经典的sql面试题

最终的sql

select r.*,sum(l.sum_sal) from ( (select name,mon,sum(sal) as sum_sal from sales_view group by name,mon) l inner join (select name,mon,sum(sal) as sum_sal from sales_view group by name,mon) r on l.name=r.name) where l.mon<=r.mon group by r.name,r.mon