哪位铁铁可以给解答一下啊，这个是真的超过我的能力范畴了，这个的正确答案是什么，是图片，应该可以加载出来的

Question

哪位铁铁可以给解答一下啊，这个是真的超过我的能力范畴了，这个的正确答案是什么，是图片，应该可以加载出来的

Answer 1

表一
create table t_ckzh (
zhdh string,
zhmc string,
bz string,
khzh string,
zhzt int
)

表二
create table t_zhye(
zhdh string,
zhye decimal(10,2)
)

表三
create table t_hl(
bz string,
hl decimal(10,2)
)

1、

select
t1.zhdh,
t1.zhmc,
t1.bz,
t1.khzh,
t1.zhzt,
nvl(t2.zhye,0)
from t_ckzh t1
left join t_zhye t2
on t1.zhdh = t2.zhdh

2、

select
t1.khzh,
t1.zhdh,
t1.zhmc,
t1.zhzt,
nvl(t2.zhye,0) as zhye,
t3.hl,
nvl(t2.zhdh*t3.hl,0)
from t_ckzh t1
left t_zhye t2
on t1.zhdh = t2.zhdh
left join t_hl t3
on t1.bz = t3.bz
where t1.khzh = '天河支行'

3、

select
zhdh,zhmc,bz,khzh,zhye，
from (
select
t1.zhdh,
t1.zhmc,
t1.bz,
t1.khzh,
t1.zhzt,
nvl(t2.zhye,0) as zhye，
row_number() over(order by t2.zhye desc) rn
from t_ckzh t1
left join t_zhye t2
on t1.zhdh = t2.zhdh
)t3
where rn =1

4、

select
khzh,khzhs,sum(zhye) as zhhzye
from (
select
t1.zhdh,
t1.zhmc,
t1.bz,
t1.khzh,
t1.zhzt,
nvl(t2.zhye,0) as zhye,
count(1) over(partition by khzh ) khzhs
from t_ckzh t1
left join t_zhye t2
on t1.zhdh = t2.zhdh
)t4
where t4.khzhs<2
group by khzh,khzhs

5、

select sum(zhye) from (
select
t1.zhdh,
t1.zhmc,
t1.bz,
t1.khzh,
t1.zhzt,
nvl(t2.zhye,0) as zhye
from t_ckzh t1
left join t_zhye t2
on t1.zhdh = t2.zhdh
where t1.zhzt=2
)t4

6、

select
khzh,sum(if(zhzt=0,zhye,0)) zczhyezh,
sum(if(zhzt=2,zhye,0)) xhzhyezh
from (
select
t1.zhdh,
t1.zhmc,
t1.bz,
t1.khzh,
t1.zhzt,
nvl(t2.zhye,0) as zhye
from t_ckzh t1
left join t_zhye t2
on t1.zhdh = t2.zhdh
) t3
group by khzh

7、

select
zhdh,zhmc,bz,khzh,zhye
from(
select
t1.zhdh,
t1.zhmc,
t1.bz,
t1.khzh,
t1.zhzt,
nvl(t2.zhye,0) as zhye
from t_ckzh t1
left join t_zhye t2
on t1.zhdh = t2.zhdh
where zhdh <> 'AAA'
)t3 where zhye >100