ORACLE分析函数

1、列转行函数listagg():

这是一个Oracle的列转行函数：LISTAGG()

实例1：

with HB_MEDICAL as(
  select '一次' DAYTIME, '2片' MEDICALNUM ,'20131022001' VISITCODE,'珍菊降压片' MEDICAL from dual union all
  select '二次' DAYTIME, '3片' MEDICALNUM ,'20131022001' VISITCODE,'复方降压片' MEDICAL from dual union all
  select '三次' DAYTIME, '4片' MEDICALNUM ,'20131022001' VISITCODE,'兰迪' MEDICAL from dual union all
  select '四次' DAYTIME, '5片' MEDICALNUM ,'20131022002' VISITCODE,'硝苯地平' MEDICAL from dual union all
  select '五次' DAYTIME, '6片' MEDICALNUM ,'20131022002' VISITCODE,'氨氯地平' MEDICAL from dual union all
  select '六次' DAYTIME, '7片' MEDICALNUM ,'20131022003' VISITCODE,'(基)(零甲)酒石酸美托洛尔片(20#*25MG)' MEDICAL from dual 
)
SELECT HM.VISITCODE,
       LISTAGG(HM.MEDICAL, ',') WITHIN GROUP(ORDER BY VISITCODE) OVER(PARTITION BY VISITCODE) LASTMEDICION
  FROM HB_MEDICAL HM

结果1：

规则：LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

当在你不实用Group by语句时候，也可以使用LISTAGG函数，配合over分析函数子句：

实例2

with HB_MEDICAL as(
  select '一次' DAYTIME, '2片' MEDICALNUM ,'20131022001' VISITCODE,'珍菊降压片' MEDICAL from dual union all
  select '二次' DAYTIME, '3片' MEDICALNUM ,'20131022001' VISITCODE,'复方降压片' MEDICAL from dual union all
  select '三次' DAYTIME, '4片' MEDICALNUM ,'20131022001' VISITCODE,'兰迪' MEDICAL from dual union all
  select '四次' DAYTIME, '5片' MEDICALNUM ,'20131022002' VISITCODE,'硝苯地平' MEDICAL from dual union all
  select '五次' DAYTIME, '6片' MEDICALNUM ,'20131022002' VISITCODE,'氨氯地平' MEDICAL from dual union all
  select '六次' DAYTIME, '7片' MEDICALNUM ,'20131022003' VISITCODE,'(基)(零甲)酒石酸美托洛尔片(20#*25MG)' MEDICAL from dual 
)
SELECT HM.DAYTIME,
       HM.MEDICALNUM,
       HM.VISITCODE,
       LISTAGG(HM.MEDICAL, ',') WITHIN GROUP(ORDER BY VISITCODE) OVER(PARTITION BY VISITCODE) LASTMEDICION
  FROM HB_MEDICAL HM

结果2：

总结：LISTAGG()把它当作SUM()函数来求字符串和。