变成
实用的sql 为
select canal,
(select A.ECL_VALUE from BEIYIN_BYJG A where A.flag=2 and A.canal=BEIYIN_BYJG.canal) as base_ecl,
(select A.ECL_VALUE from BEIYIN_BYJG A where A.flag=3 and A.canal=BEIYIN_BYJG.canal) as optimism_ecl,
(select A.ECL_VALUE from BEIYIN_BYJG A where A.flag=4 and A.canal=BEIYIN_BYJG.canal) as pessimism_ecl
from BEIYIN_BYJG group by canal
稍作解释 我这种方式比较笨的其实 ,用了group by ,因为我知道同一个canal下的一个flag只能对应一个,因为他们本身就是行,除非有重复数据存在才会多,但那时不可能的。
如果不加group by 就会出现
可以到效果了吧,group by 其实也就是去重用的,还可以这样
效果和group by 一样
顺便提一句,我饶了一个弯路 :
我之前把代码这么写来着,如果不作为子表被关联的话,还能用,但并不能替换那种用group by 和distinct去重的实用。
您也可以试试哦。
<select id="XXX" parameterType="XXXX" resultType="XXXXX">
select
plan.PLAN_NAME planName,ecl.PLAN_UUID planUuid,ecl.DATA_DATE countDate,credit.CANAL canal,
to_char(sum(credit.CREDIT_LINE),'9999999999990.00') credit,
to_char(sum(credit.AVAILABLE_CREDIT),'9999999999990.00') availableCredit,
to_char(sum(credit.AVAILABLE_CREDIT)*0.2,'9999999999990.00') eadValue,
to_char(sum(byjg.base_ecl),'9999999999990.00') baseEcl,
to_char(sum(byjg.pessimism_ecl),'9999999999990.00') pessimismEcl,
to_char(sum(byjg.optimism_ecl),'9999999999990.00') optimismEcl ,
to_char(sum(byjg.base_ecl)+sum(byjg.pessimism_ecl)+sum(byjg.optimism_ecl),'9999999999990.00') eclValue
from I9_ECL_DETAIL ecl
inner join CREDIT_LIMIT credit on credit.COUSTOM_CODE = ecl.COUSTOM_CODE
left join (
select canal,
(select A.ECL_VALUE from BEIYIN_BYJG A where A.flag=2 and A.canal=BEIYIN_BYJG.canal) as base_ecl,
(select A.ECL_VALUE from BEIYIN_BYJG A where A.flag=3 and A.canal=BEIYIN_BYJG.canal) as optimism_ecl,
(select A.ECL_VALUE from BEIYIN_BYJG A where A.flag=4 and A.canal=BEIYIN_BYJG.canal) as pessimism_ecl
from BEIYIN_BYJG group by canal
) byjg on byjg.canal = credit.canal
left join (
select plan.PLAN_UUID PLAN_UUID ,child.PLAN_UUID PARENT_PLAN_UUID ,plan.PLAN_NAME PLAN_NAME from I9_PLAN plan inner join I9_PLAN child on child.PARENT_PLAN_UUID = plan.PLAN_UUID
) plan on ecl.PLAN_UUID = plan.PARENT_PLAN_UUID
<where>
XXXXXX
</where>
GROUP BY ecl.PLAN_UUID,ecl.DATA_DATE,plan.PLAN_NAME,credit.CANAL
</select>
如图代码是我所负责的实战项目中的一角,我隐去了一些不关乎知识点的信息。
最难写的是 byjg 这个虚拟表。
BEIYIN_BYJG 这个是实体表 有 ECL_VALUE, canal,flag 三个字段,而我需要根据一个 canal 找到 3个ECL_VALUE 他们 2为基准,3为乐观,4为悲观