oracle 行转列

 

 

变成 

 

实用的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为悲观