查询每个银行内的存款总额（不包括贷款、保险）、所有贷款的总额、存款最多的存款金额、贷款总额与存款总额之比；
输出：branch_id，存款总额，贷款总额，存款最多的存款金额，贷款总额:存款总额
示例：
±---------------±---------------±-------------±--------------±-----------+
| OPEN_BRANCH_ID | saving | loans | maxs | precent |
±---------------±---------------±-------------±--------------±-----------+
| 2 | 231201772.6167 | 0.0000 | 99021770.1967 | 0.00000000 |
±---------------±---------------±-------------±--------------±-----------+
注：该示例不是标准答案
SELECT t.*, t.loans/t.saving as ratio from(
SELECT a.OPEN_BRANCH_ID,sum(if(pp.name='存款',a.AVAIL_BALANCE,0)) as deposits,
sum(if(pp.name='贷款',a.AVAIL_BALANCE,0)) as loans,
max(if(pp.name='存款',a.AVAIL_BALANCE,0)) as max_deposit
from account a
JOIN PRODUCT p on p.PRODUCT_CD = a.PRODUCT_CD
join product_type pp on pp.product_type_cd=p.product_type_cd
GROUP BY OPEN_BRANCH_ID
) as t
select branch_id, saving, maxs, coalesce(loan, 0) loan, ( ifnull(sec.loan, 0) / fir.saving) as percent from
(
(select BRANCH_ID, sum(avail_balance) saving, max(avail_balance) maxs
from (branch b, account a, product p)
where b.branch_id = a.open_branch_id
and a.product_cd = p.product_cd
and p.product_type_cd = 'ACCOUNT'
group by BRANCH_ID) as fir
left outer join
(
select BRANCH_ID, IFNULL(sum(avail_balance), 0) loan
from (branch b, account a, product p)
where b.branch_id = a.open_branch_id
and a.product_cd = p.product_cd
and p.product_type_cd = 'LOAN'
group by BRANCH_ID
) as sec
using(branch_id)
);
请使用with rescusive 递归查询每个员工的上级领导。输出员工id、上级领导｜该员工ID。
答案示例：
±-------±------------+
| emp_id | res |
±-------±------------+
| 1 | 1 |
| 2 | 1|2 |
| 3 | 1|3 |
| 20 | 1|2|20 |
| 4 | 1|3|4 |
±-------±------------+
注：示例是标准答案部分数据
with recursive t(emp_id,res) as(
(select emp_id ,cast(emp_id as char ) from employee where superior_emp_id is null )
union all (select e.emp_id,CONCAT(t.res,'|',e.emp_id)as res from t,employee e where t.emp_id = e.superior_emp_id ))
select * from t;
select * from employee;
select emp_id, cast(coalesce(concat(superior_emp_id, '|', emp_id), emp_id) as char(30)) as res
from employee;
WITH recursive cte(emp_id, res) as
(
select e.emp_id , coalesce(concat(superior_emp_id, '|', emp_id), emp_id) as res
from employee e
where e.superior_emp_id is NULL
union
select e.emp_id , concat(res, '|', e.emp_id)
from employee e inner join cte
where e.superior_emp_id = cte.emp_id
)
select * from cte;
利用游标修改account表的AVAIL_BALANCE；如果该金额是属于存款，则按利息每年利息0.00001增加，以last_activity_date开始计算。
注：先更新一下account表
update account set last_activity_date=‘2022-11-11’ where last_activity_date is null;
提示：使用函数 timestampdiff
CREATE DEFINER = `root` @`localhost` PROCEDURE `proc_cursor2` () DETERMINISTIC BEGIN
DECLARE done INT DEFAULT ( 0 );
DECLARE accountid INT;
DECLARE n INT;
DECLARE i INT;
DECLARE ans DECIMAL ( 12, 4 );
DECLARE cur_1 CURSOR FOR SELECT
ACCOUNT_ID ,AVAIL_BALANCE FROM account;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET done = 1;
OPEN cur_1;
REPEAT
set i = 0;
FETCH cur_1 INTO accountid,ans;
SELECT timestampdiff(YEAR,last_activity_date,now()) into n
FROM account WHERE ACCOUNT_ID = accountid;
WHILE i < n DO
set ans = ans * 0.00001;
set i = i+1;
END WHILE;
UPDATE account
SET AVAIL_BALANCE = ans
WHERE
account.ACCOUNT_ID = accountid;
UNTIL done
END REPEAT;
CLOSE cur_1;
END
SET SQL_SAFE_UPDATES = 0;
update account set last_activity_date='2022-11-11' where last_activity_date is null;
select * from account;
update account set avail_balance = 10000 where avail_balance is null;
desc account;
drop procedure if exists updateAB;
delimiter $$
create procedure updateAB()
begin
declare ai int;
declare ab decimal(12, 4);
declare lad date;
declare dif int;
declare cur cursor for
select account_id, avail_balance, last_activity_date
from account;
DECLARE exit HANDLER FOR NOT FOUND CLOSE cur;
open cur;
repeat
fetch cur into ai, ab, lad;
set dif = timestampdiff(year, lad, NOW());
while(dif > 0) do
set ab = ab * 1.00001;
set dif = dif - 1;
end while;
-- select dif;
update account set avail_balance = ab
where account_id = ai;
until null
end repeat;
close cur;
end $$
delimiter ;
4
新建日志表
CREATE TABLE `logs` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`log` varchar(255) DEFAULT NULL COMMENT '日志说明',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';
在players表(第七次作业中已建好的表)中新建update触发器：
写好触发器后，请依次运行如下SQL语句，检查触发器运行结果：
SELECT * from teams;
UPDATE players set salary = 10*salary,team_id=3 WHERE `name`="姚明";
SELECT * from teams;
SELECT * from logs;
DROP TRIGGER IF EXISTS `player_log`;
delimiter;;
CREATE TRIGGER `player_log` AFTER UPDATE ON `players` FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR ( 1000 ) CHARACTER
SET utf8;
DECLARE
s2 VARCHAR ( 20 ) CHARACTER
SET utf8;# 为避免中文字符编码出现乱码，这里需要设置字符集
DECLARE
res VARCHAR ( 40 ) CHARACTER
SET utf8;
SET s2 = " is updated to ";
SET s1 = "";# 依次检测一条update操作是否对player的三个属性进行变更（id除外）
IF
old.NAME <> new.NAME THEN
SET s1 = CONCAT( s1, "name ", old.NAME, s2, new.NAME, "; " );
END IF;
IF
old.salary <> new.salary THEN
SET s1 = CONCAT( s1, "salary ", old.salary, s2, new.salary, "; " );
END IF;
IF
old.team_id <> new.team_id THEN
SET s1 = CONCAT( s1, "teams_id ", old.team_id, s2, new.team_id, "; " );
UPDATE teams
SET nums = nums - 1
WHERE
teams.id = old.team_id;
UPDATE teams
SET nums = nums + 1
WHERE
teams.id = new.team_id;
END IF;
INSERT INTO LOGS(log) VALUES(s1);
END;
;;
delimiter;
drop trigger if exists update_player ;
delimiter $$
create trigger update_player
after update
on players
for each row
begin
set @temp = '';
if old.id != new.id then
set @temp = concat( @temp, "id ", old.id, " is update to ", new.id, '; ');
end if;
if old.name != new.name then
set @temp = concat( @temp, " name ", old.name, " is update to ", new.name, '; ');
end if;
if old.salary != new.salary then
set @temp = concat( @temp, " salary ", old.salary , " is update to ", new.salary , '; ');
end if;
if old.team_id != new.team_id then
set @temp = concat( @temp, " team_id ", old.team_id , " is update to ", new.team_id , '; ');
update teams set teams.nums = teams.nums + 1 where teams.id = new.team_id;
update teams set teams.nums = teams.nums - 1 where teams.id = old.team_id;
end if;
if @temp != "" then
insert into logs(log) values(@temp);
end if;
end $$
delimiter ;