|
1、查询每个账户的折人民币余额，返回：账户代号、账户名称、币种、开户机构、账户状态、账户折人民币余额 select a.*,b.zhye*c.hl as '折人民币余额' from t_ckzh a join t_zhye b on a.zhdh = b.zhdh join t_hl c on a.bz = c.bz 2、查询账户折人民币余额最大的账户信息，返回：账户代号、账户名称、币种、开户机构、账户余额 select a.*,b.zhye*c.hl as '折人民币余额' from t_ckzh a join t_zhye b on a.zhdh = b.zhdh join t_hl c on a.bz = c.bz where b.zhye*c.hl = ( select max(y.zhye*z.hl) from t_ckzh x join t_zhye y on x.zhdh = y.zhdh join t_hl z on x.bz = z.bz ) 3、查询开户账户数少于2个的机构相关信息，返回：开户机构、开户账户数、存款总余额（折人民币） SELECT a.khjg,COUNT(*) as '开户账户数',b.zhye*c.hl as '存款总余额' from t_ckzh a join t_zhye b on a.zhdh = b.zhdh join t_hl c on a.bz = c.bz GROUP BY a.khjg HAVING count(*) <2 4、假设账户状态=0，表示“正常账户”，账户状态=2表示“销户账户”，请查询销户总余额，返回：销户总余额 select sum(b.zhye*c.hl) as '销户总金额' from t_ckzh a join t_zhye b on a.zhdh = b.zhdh join t_hl c on a.bz = c.bz GROUP BY a.zhzt HAVING a.zhzt = 0 5、假设账户状态=0，表示“账户正常”，账户状态=2表示“账户销户”，请查询不同机构下，账户不同状态的余额，返回：开户机构、正常账户余额之和、销户账户余额之和 select a.khjg, if(a.zhzt=0,'正常余额','异常余额') as '账号情况',sum(b.zhye*c.hl) as '账户余额' from t_ckzh a join t_zhye b on a.zhdh = b.zhdh join t_hl c on a.bz = c.bz GROUP BY a.zhzt,a.khjg 6、账户余额（折人民币）高于AAA账户的账户信息，返回账户代号、账户名称、币种、开户机构、账户余额 select a.*,b.zhye*c.hl as '账号余额' from t_ckzh a join t_zhye b on a.zhdh = b.zhdh join t_hl c on a.bz = c.bz where b.zhye*c.hl >( select y.zhye*z.hl from t_ckzh x join t_zhye y on x.zhdh = y.zhdh join t_hl z on x.bz = z.bz where x.zhdh = 'AAA' ) 这几个题目只是学习研究之用，这几个题目亲自在数据库运行过，没有问题，欢迎各位志同道合的朋友指出不足之处，谢谢！！！！ (责任编辑：) |
