MySQL实训3

表1：货款基本信息表

LOAN_NO	ID_NO	ACTV_DT
1000114260	1	2011-06-07
1000143723	2	2011-09-21
1000162024	3	2011-12-09
1000174934	4	2012-03-23
1000182256	5	2012-05-15

LOAN_NO:贷款编号
ID_NO：贷款人身份编号
ACTV_DT：放款日期

#建表
mysql>  create table a1(LOAN_NO INT,ID_NO INT,ACTV_DT DATE);

#查询表结构
mysql> desc a1;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| LOAN_NO | int(11) | YES  |     | NULL    |       |
| ID_NO   | int(11) | YES  |     | NULL    |       |
| ACTV_DT | date    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

#插入数据
mysql>  insert into a1 values
    -> (1000114260,1,'2011-06-07'),
    -> (1000143723,2,'2011-09-21'),
    -> (1000162024,3,'2011-12-09'),
    -> (1000174934,4,'2012-03-23'),
    -> (1000182256,5,'2012-05-15');

#查询数据
mysql> select * from a1;
+------------+-------+------------+
| LOAN_NO    | ID_NO | ACTV_DT    |
+------------+-------+------------+
| 1000114260 |     1 | 2011-06-07 |
| 1000143723 |     2 | 2011-09-21 |
| 1000162024 |     3 | 2011-12-09 |
| 1000174934 |     4 | 2012-03-23 |
| 1000182256 |     5 | 2012-05-15 |
+------------+-------+------------+

表2：贷款逾期天数表

LOAN_NO	OD_DAYS
1000114260	90
1000174934	16
1000182256	0
1000143723	45
1000162024	3

LOAN_NO：贷款编号
OD_DAYS:贷款逾期天数

#建表
mysql> create table a2(LOAN_NO INT,OD_DAYS INT);

#查询表结构
mysql> desc a2;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| LOAN_NO | int(11) | YES  |     | NULL    |       |
| OD_DAYS | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+

#插入数据
mysql>  insert into a2 values
    -> (1000114260,90),
    -> (1000174934,16),
    -> (1000182256,0),
    -> (1000143723,45),
    -> (1000162024,3);
    
#查询数据
mysql> select * from a2;
+------------+---------+
| LOAN_NO    | OD_DAYS |
+------------+---------+
| 1000114260 |      90 |
| 1000174934 |      16 |
| 1000182256 |       0 |
| 1000143723 |      45 |
| 1000162024 |       3 |
+------------+---------+

表3：贷款人信用记录表

ID_NO	LIM	OUTSTANDING
5	30000	25110
2	40000	45000
2	50000	56000
2	45000	45000
1	15000	6378
1	80000	60395
3	60000	57773
4	30000	28656
4	30000	10000

ID_NO:贷款人身份编号
LIM: 贷款人在我司贷款之前在其他小额贷款公司贷款金额
OUTSTANDING:贷款人在我司贷款之前在其他小额贷款公司贷款金额中未偿还部分

#建表
mysql> create table a3(ID_NO INT,LIM INT,OUTSTANDING INT);

#查询表结构
mysql> desc a3;
+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| ID_NO       | int(11) | YES  |     | NULL    |       |
| LIM         | int(11) | YES  |     | NULL    |       |
| OUTSTANDING | int(11) | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

#插入数据
mysql> insert into a3 values
    -> (5,30000,25110),
    -> (2,40000,40000),
    -> (2,60000,56000),
    -> (2,45000,45000),
    -> (1,15000,6378),
    -> (1,80000,60395),
    ->  (3,60000,57773),
    -> (4,30000,28656),
    -> (4,30000,10000);

#查询数据
mysql> select * from a3;
+-------+-------+-------------+
| ID_NO | LIM   | OUTSTANDING |
+-------+-------+-------------+
|     5 | 30000 |       25110 |
|     2 | 40000 |       40000 |
|     2 | 60000 |       56000 |
|     2 | 45000 |       45000 |
|     1 | 15000 |        6378 |
|     1 | 80000 |       60395 |
|     3 | 60000 |       57773 |
|     4 | 30000 |       28656 |
|     4 | 30000 |       10000 |
+-------+-------+-------------+

要求：利用表1、表2、表3经过处理, 形成表4。
表4中TYPE字段的要求：根据贷款逾期天数定义贷款类型（如果贷款逾期天数等于0则贷款类型为CURRENT；如果贷款逾期天数大于0且小于等于89则贷款类型为MIDDLE；如果贷款逾期天数大于等于90则贷款类型为CHRGO）

表4

LOAN_NO	ID_NO	ACTV_DT	OD_DAYS	TYPE	LIM	OUTSTANDING
1000114260	1	2011-06-07	90	CHRGO	95000	66773
1000143723	2	2011-09-21	45	MIDDLE	145000	141000
1000162024	3	2011-12-09	3	MIDDLE	60000	57773
1000174934	4	2012-03-23	18	MIDDLE	60000	38886
1000182256	5	2012-05-15	0	CURRENT	30000	25110

① LOAN_NO、ID_NO、ACTV_DT来自表a1，OD_DAYS来自表a2，LOAN_NO可将表a1和表a2连接起来，LIM、OUTSTANDING来自表a3,ID_NO可将表a1和表a3连接起来
② TYPE字段需要借助if语句实现
③ 因为表a4中的LIM不是单次的LIM，故需要对ID_NO分类计算同一个ID_NO的LIM总和。由于在本题中ID_NO和LOAN_NO一一对应，也可用LOAN_NO来分类，结果相同。

mysql> select a1.LOAN_NO,a1.ID_NO,ACTV_DT,OD_DAYS,
    -> IF(OD_DAYS=0,'CURRENT',IF(OD_DAYS>=90,'CHRGO','MIDDLE')) AS TYPE,
    -> SUM(LIM),OUTSTANDING
    -> FROM a1 left join a2 on a1.LOAN_NO = a2.LOAN_NO   left join a3 on a1.ID_NO = a3.ID_NO
    -> GROUP BY  a3.ID_NO;
+------------+-------+------------+---------+---------+----------+-------------+
| LOAN_NO    | ID_NO | ACTV_DT    | OD_DAYS | TYPE    | SUM(LIM) | OUTSTANDING |
+------------+-------+------------+---------+---------+----------+-------------+
| 1000114260 |     1 | 2011-06-07 |      90 | CHRGO   |    95000 |        6378 |
| 1000143723 |     2 | 2011-09-21 |      45 | MIDDLE  |   145000 |       40000 |
| 1000162024 |     3 | 2011-12-09 |       3 | MIDDLE  |    60000 |       57773 |
| 1000174934 |     4 | 2012-03-23 |      16 | MIDDLE  |    60000 |       28656 |
| 1000182256 |     5 | 2012-05-15 |       0 | CURRENT |    30000 |       25110 |
+------------+-------+------------+---------+---------+----------+-------------+