SQL-Interview-Questions1

2020年7月 杭州晨科技术有限公司出给我的面试题

一、题目如下

这有一个面试题，你看看能不能做出来。一个房产代理公司，同时代理了多个楼盘项目，其业务员可以同时负责任意楼盘的销售。现在该公司想核算每个楼盘每个月的人员工资成本，核算规则是：如果业务员当月未出单，则按所有楼盘数量平摊该业务员的工资；如果该业务员有出单，则按每个楼盘的出单数量均摊业务员的工资，未出单的楼盘不核算成本。请构建数据库模型，并用sql完成，不能用if逐条处理。
提示：需要用到group by，sum，count，一共五张表，楼盘表（id、楼盘名称），业务员表（id，姓名），销售业绩表（月份，业务员、楼盘、销售数量），工资表（月份，业务员、工资），成本核算表（月份，楼盘、工资成本），最终结果写入到成本核算表，有需要的话表里可以增加字段，可以用多条sql来实现
假设一共5个楼盘，如果业务员张三工资是5000，一个月没出一单，则每个楼盘摊1000的成本。如果业务员李四工资是6000，两个楼盘各出1单和2单，其他楼盘没出单，则出单的两个楼盘分别摊2000和4000的成本，其他三个楼盘摊的成本为0。这个面试题你要是能做出来，可以约个时间过来面试

二、我数据库前期准备

#楼盘表
create table building (
build_id int primary key,
build_name VARCHAR(50));
#业务员表
create table salesman (
salesman_id int primary key,
salesman_name VARCHAR(50));
#销售业绩表
create table sales_performance(
month_id int,
salesman_id int,
build_id int,
sale int);
#工资表
create table salary(
month_id int,
salesman_id int,
salary int);
#成本核算表
create table cost_accounting(
month_id int,
build_id int,
salary_cost int);
#插入测试数据
插入5个楼盘的数据
Insert into building values(1,"build1");
Insert into building values(2,"build2");
Insert into building values(3,"build3");
Insert into building values(4,"build4");
Insert into building values(5,"build5");
#插入业务人员数据
insert into salesman values(1,'zhangsan');
insert into salesman values(2,'lisi');
#插入业务员的工资
insert into salary values(202007,1,5000);
insert into salary values(20200,2,5000);
#楼盘销售
insert into sales_performance values(202007,1,1,0);
insert into sales_performance values(202007,1,2,0);
insert into sales_performance values(202007,1,3,0);
insert into sales_performance values(202007,1,4,0);
insert into sales_performance values(202007,1,5,0);
insert into sales_performance values(202007,2,1,2);
insert into sales_performance values(202007,2,2,3);
insert into sales_performance values(202007,2,3,0);
insert into sales_performance values(202007,2,4,0);
insert into sales_performance values(202007,2,5,0);

三、我个人答案版本，使用一个 sql 语句完成操作，代码结构复杂

insert into cost_accounting 
select month_id,build_id,
# 计算开工人员的成本,使用case将除数为0取出掉
sum(case salesman_sum_sale when '0' then '0' else salary/salesman_sum_sale*build_sum_sale end)+
# 计算没有开工的人员的成本
(select sum(salary)/(select count(*) from building) from salary where month_id=date_format(now(),'%Y%m') and salesman_id in (
 Select salesman_id from (select salesman_id,sum(sale) as sale from sales_performance 
where month_id= date_format(now(),'%Y%m') group by salesman_id) as s where s.sale=0)) 
from  (
# 该查询获得，当月的编号、人员编号、楼盘编号、人员对应某一楼盘的销售单数build_sum_sale、人员工资salary、某一员工的总单数 salesman_sum_sale
(select month_id ,salesman_id,build_id,sum(sale) as build_sum_sale  from sales_performance 
where month_id= date_format(now(),'%Y%m') group by salesman_id,build_id)  as a  
left join (select salesman_id,salary from salary where month_id= date_format(now(),'%Y%m'))  as b
on a.salesman_id=b.salesman_id 
left join (select salesman_id,sum(sale) as salesman_sum_sale from sales_performance 
where month_id= date_format(now(),'%Y%m')  group by salesman_id) as c
on a.salesman_id=c.salesman_id
) group by build_id;

四、其他面试人员版本，使用一个临时表和两个 sql 语句完成操作，代码结构较为清晰明了

建临时表，字段包含月份，楼盘，业务员，工资成本  
1.未开单人员 insert into 临时表 select d.月份,d.楼盘名称,b.业务员,a.工资/c.estate_num from 工资表 a inner join (select 月份，业务员，sum(销售数量) as sale_num from 销售业绩表 group by 月份，业务员 having sum(销售数量)=0)b  on a.月份=b.月份 and a.业务员=b.业务员 inner join  (select 月份,count(distinct id) as estate_num from 楼盘表 group by 月份)c on a.月份=b.月份 inner join 楼盘表 d on a.月份=d.月份  
2.开单人员  insert into 临时表 select x.月份,x.楼盘名称,x.业务员,x.avg_salary*y.销售数量 from  (select c.月份,c.楼盘名称,b.业务员,a.工资/b.sale_num as avg_salary from 工资表 a inner join (select 月份，业务员，sum(销售数量) as sale_num from 销售业绩表 group by 月份，业务员 having sum(销售数量)>0)b  on a.月份=b.月份 and a.业务员=b.业务员 inner join 楼盘表 c on a.月份=c.月份)x inner join 销售业绩表 y on x.月份=y.月份 and x.楼盘名称=y.楼盘名称 and x.业务员=y.业务员  
3.汇总计算 insert into 成本核算表 select 月份,楼盘名称,sum(工资成本) from 临时表 group by 月份,楼盘名称