oracle 将获取到的两行数据，拼接为两列

如果光看题目的话，肯定会感觉很简单，但是运用到实际中就很麻烦了。

需求：主表对应子表 一对多，现在对应主表一条记录，可能对应子表多条记录，将这些记录汇总到一行中，考虑到主表记录可能比较多，只需要，回去两个就可以了。

先来看一下我的查询语句：

 

select V.memvipmember_id, V.vipcode, decode(V.vipsource, 1, '商场', 2, '微信', 3, '其他') as vipsource,
       V.vipname, cast(decode(V.vipstatus, 1, '正常', 2, '不正常', 3, '作废') as varchar2(10)) as vipstatus,
       decode(V.vipgender, 1, '男', 2, '女') as vipgender, V.vipfamilyphone, V.vipmobile,
       '' as verifytelno, V.vipemail,
       cast(decode(V.passtype, 1, '身份证', 2, '护照', 3, '军官证') as varchar(10)) as passtype, V.passno,
       PE.PUBBE002 as city_idc1, PF.PUBBF001 as pubbg003c1, PG1.PUBBG002 as houses1c1,
       PG2.PUBBG002 as houses2c1, PG3.PUBBG002 as houses3c1,
       x.address as address1,x1.address as address2
  from TB_MEMVIPMEMBER V
  LEFT JOIN TB_PUBBG PG1 on V.houses1 = PG1.PUBBG_ID
  LEFT JOIN TB_PUBBG PG2 on V.houses2 = PG2.PUBBG_ID
  LEFT JOIN TB_PUBBG PG3 on V.houses3 = PG3.PUBBG_ID
  LEFT JOIN TB_PUBBE PE on V.city_id = PE.PUBBE_ID
  LEFT JOIN TB_PUBBF PF on PG1.pubbg003 = PF.pubbf_id
  left join (
       select sub5.vipmember_id,sub5.address,row_number()over(partition by sub5.vipmember_id order by sub5.modi_date) rn from TB_MEMVIPMEMBERSUB5 sub5 
  ) x on x.vipmember_id = v.memvipmember_id and x.rn = 1
  left join (
       select sub5.vipmember_id,sub5.address,row_number()over(partition by sub5.vipmember_id order by sub5.modi_date) rn from TB_MEMVIPMEMBERSUB5 sub5 
  ) x1 on x1.vipmember_id = v.memvipmember_id and x1.rn = 2
 where 1 = 1  
 order by V.vipcode

 

这里面只显示了查询部分，没有显示条件部分，要实现我想要的结果的地方是最后两个left join 语句，第一个识货对应的第一个地址信息，第二个是获取第二个地址信息，

有可能地址信息都为空这里面使用到了 row_number 函数，具体的用法，读者自己去查，主要功能是分组，获取对应的列是第几列，然后在外部的条件语句中进行限制

这样可以获取到你想要的任何列，这个也可用用于取号段，对应会员卡和连续的号段特别有帮助。

下面是第二种方法：

写的比较简单：

SELECT  
address ,vipmember_id,
substr(address, instr(address,',')+1) as address1,

substr(address, 0,instr(address,',')-1) as address2

FROM ( 
			SELECT wm_concat (address) AS address,vipmember_id FROM ( 
			SELECT address, vipmember_id FROM TB_MEMVIPMEMBERSUB5 WHERE flag = 0 ORDER BY modi_date DESC ) 
			V5 WHERE ROWNUM < 3 AND vipmember_id = 203 GROUP BY (vipmember_id)
     ) 
where 1=1

 

这里用的是oracle中的几个函数，主要是wm_concat 、instr、substr以及length，具体的用法读者自己去查，wm_concat主要是将分组的信息汇总到一列，以"," 分割，最里面的是获取所用满足的信息，其次内部是要完成wm_concat功能，外面是将对应的信息进行分割，获取“,”的位置然后分割，就可以获取到对应，这个方法的效率没有上面的高，但是没有方法二容易理解，对于高手就另当别论了。