今天讲解一道经典的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个月的张三的销售额
如图:
这样我们计算累计金额的时候,就可以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
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