昨晚阿里巴巴的一道面试题分析

昨晚参加阿里巴巴数据开发工程师的一道sql语句题搞蒙了，今早重新梳理了一下和大家分享一下：

【题】

蚂蚁金服需要评估用户的信用风险，用于为用户提供基于信用的各种服务。每个用户有一些基本信息（比如学历，年龄等）。另外每个用户存在一个或者多个支付宝账号，其中一个账号会作为用户的主账号， 假设主账号的规则是账户余额最多的账号，如果两个账户余额相同，则选择注册时间较早的账户。具体表结构设计如下：

用户表(users),账户信息表(accounts)

create table users(user_id int,age int,degree char(1));

create table accounts(id int,user_id int,balance double,year int);

1） 假设用户的信用分是基于一下规则计算的： 

a） 基础分为300分

b） 年龄在[18-30]之间，增加10分；在 [31-59]之间，增加20分

c） 学历为A等级增加20分；学历为B等级增加10分

d） 主账户的余额大于等于100000，增加20分；[10000-99999]，增加10分

e） 主账户的注册时间为5年以上增加20分；1年到5年之间增加10分

请用SQL语句，计算每个用户的信用分。

【思路】

 第一眼想到用case.....when...但这个之前确实没怎么用过，不太熟悉，先建立一个表测试一下该语句用法：

测试表：测试case when then else end的用法

create table tt(id int,name varchar(20),sal float,des varchar(20));
insert into tt values(1,'n',1222,'beijing');
insert into tt values(2,'nn',122,'beijing');
insert into tt values(3,'nnn',112,'hunan');
insert into tt values(4,'nnnn',11,'hunan');

select 300+c+b from
(select id,sal,
(case 
when id<=2 then 2 
when id>2 then 3 end ) as c ,
(case 
when sal<=120 then 9 
when sal>120 then 5 end ) as b from tt ) s where s.id=2;

创建题目中的主表：

create table users(user_id int,age int,degree char(1));
create table accounts(id int,user_id int,balance double,year int);

插入测试数据：

insert into users values(1,25,'A');
insert into accounts  values(1,1,300000,4);
insert into accounts  values(2,1,30000,3);
insert into accounts  values(3,1,5000,2);

insert into users  values(2,30,'B');
insert into accounts  values(4,2,28000,2);

insert into users  values(3,28,'B');
insert into accounts  values(5,3,44000,5);

解决问题：

select 300+ag+de+ba+ye from 
(select s.user_id,
(case 
when  18<=age<=30 then 10
when  31<=age<=59 then 20 end ) as ag ,
(case 
when  degree='A' then 20
when  degree='B' then 10 end ) as de  ,
(case 
when   balance>=100000 then 20
when  10000<=balance<99999 then 10 end ) as ba,
(case 
when   year>5 then 20
when  1<year<=5 then 10 end ) as ye from (select a.user_id,u.age,u.degree,a.balance,a.year,max(a.balance) from accounts a,users u where u.user_id=a.user_id group by a.user_id) s) ss where ss.user_id=1;

DataAnswer导航：http://www.dataanswer.top