003.检验.SQL之债务计算ITeye - 乐橙lc8

003.检验.SQL之债务计算ITeye

2019-01-11 05:10:10 | 作者: 子昂 | 标签: 债务,数据,买卖 | 浏览: 852



# 003.检验.SQL之债务计算

这是一个比较靠近实践事务的SQL操练,技能点比较常用和有用。 

作者:史荣久 日期:2014-11-26 答应:CC BY-SA 3.0 

## 任务说明

如下图,事务中,存在以下数据和数据联系: 
一个顾客(CUST)会有零或多个债务(LOAN), 
债务会有零或多个买卖(TRANSACTION)。

LOAN表BALANCE为余额,REPAYDAY为下次还款日, 
TRANSACTION表中,ID是自增的,AMOUNT为金额, 
OPRTIME为操作日,TYPE=1为借款,TYPE=2为还款。

![ER图和数据](/images/post/actions/003/er-data.png)


## 思考问题

1)各顾客的余额,用SQL得到下图输出。

![各顾客的余额](/images/post/actions/003/result-1.png)


2)还款最多买卖,即用SQL得到下图输出。 
在2010年从01-01(含)到06-01(不含)时间段, 
找出各债务还款最多的买卖数据(或许多条), 
并显现对应的顾客名,


![还款最多买卖](/images/post/actions/003/result-2.png)


3)买卖表有10亿数据,检索成果呼应很慢, 
或许是何原因,怎么排查原因,怎么改进。

4)本题中3张表,规划上有何缺点,怎么改进。

## 测试数据

经过以下SQL创立表结构和初始数据。

CREATE TABLE `CUST` (
 `CUSTID` VARCHAR(12) NOT NULL ,
 `NAME` VARCHAR(20) NOT NULL ,
 `AGE` INT(11) NOT NULL ,
 PRIMARY KEY (`CUSTID`) )
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;
CREATE TABLE `LOAN` (
 `LOANID` VARCHAR(20) NOT NULL ,
 `CUSTID` VARCHAR(12) NOT NULL ,
 `BALANCE` DECIMAL(16,4) NOT NULL ,
 `REPAYDAY` DATE NULL DEFAULT NULL ,
 PRIMARY KEY (`LOANID`) ,
 UNIQUE INDEX `ID_UNIQUE` (`LOANID` ASC) ,
 INDEX `CUST` (`CUSTID` ASC) ,
 CONSTRAINT `CUST`
 FOREIGN KEY (`CUSTID` )
 REFERENCES `CUST` (`CUSTID` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;
CREATE TABLE `TRANSACTION` (
 `ID` INT(11) NOT NULL ,
 `AMOUNT` DECIMAL(16,4) NOT NULL ,
 `TYPE` INT(11) NOT NULL ,
 `OPRTIME` DATETIME NOT NULL ,
 `LOANID` VARCHAR(20) NOT NULL ,
 PRIMARY KEY (`ID`) ,
 INDEX `LOAN` (`LOANID` ASC) ,
 CONSTRAINT `LOAN`
 FOREIGN KEY (`LOANID` )
 REFERENCES `LOAN` (`LOANID` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = INNODB DEFAULT CHARACTER SET = UTF8 COLLATE = UTF8_BIN;
INSERT INTO CUST(CUSTID, NAME, AGE) VALUES
(100000000101, 张三, 23),
(100000000201, 李四, 24),
(100000000301, 王五, 25);
INSERT INTO LOAN(LOANID, CUSTID, BALANCE, REPAYDAY) VALUES
(10000000010100000001, 100000000101, 3000, 2010-07-03),
(10000000010100000002, 100000000101, 3500, 2010-07-23),
(10000000020100000001, 100000000201, 4000, 2010-07-04);
INSERT INTO TRANSACTION(ID, AMOUNT, TYPE, OPRTIME, LOANID) VALUES
(1, -10000, 1, 2010-03-03 15:15:15, 10000000010100000001),
(2, 2000, 2, 2010-04-03 15:15:15, 10000000010100000001),
(3, 2000, 2, 2010-05-03 15:15:15, 10000000010100000001),
(4, 3000, 2, 2010-06-03 15:15:15, 10000000010100000001),
(5, -8000, 1, 2010-04-23 15:15:15, 10000000010100000002),
(7, 2000, 2, 2010-05-23 15:15:15, 10000000010100000002),
(8, 2500, 2, 2010-06-23 15:15:15, 10000000010100000002),
(9, -10000, 1, 2010-01-04 15:15:15, 10000000020100000001),
(10, 1000, 2, 2010-02-04 15:15:15, 10000000020100000001),
(11, 1000, 2, 2010-03-04 15:15:15, 10000000020100000001),
(12, 1500, 2, 2010-04-04 15:15:15, 10000000020100000001),
(13, 1500, 2, 2010-05-04 15:15:15, 10000000020100000001),
(14, 1000, 2, 2010-06-04 15:15:15, 10000000020100000001);


-----
题图:《黑客帝国》以许多方法解析实在、超现实,还有人的观念是本质的、物理的国际才是虚幻。
原文:
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表乐橙lc8立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章