爱可深思▼ 2014▼ 内测 编码 SQL 连接

A003.内测.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图和数据

思考问题

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

各顾客的余额

2)还款最多交易,即用SQL得到下图输出。
在2010年从01-01(含)到06-01(不含)时间段,
找出各债权还款最多的交易数据(可能多条),
并显示对应的顾客名,

还款最多交易

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');
《A003.内测.SQL之债权统计》 这是一个比较贴近实际业务的SQL练习,技术点比较常用和实用。
题图:《黑客帝国》以很多方式解析真实、超现实,还有人的观点是实质的、物理的世界才是虚幻。