oracle日期合并 分别用逗号或者分号隔开

LISTAGG(SUBSTR(TO_CHAR(FREESTARTTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9) ||‘~‘||trim(SUBSTR(TO_CHAR(FREEENDTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9)),‘,‘)WITHIN GROUP (ORDER BY TPF.FREEID) AS FREETIMESPAN FROM TRA_PRICINGBERTH TPB LEFT JOIN TRA_PRICINGFREE TPF ON TPB.PRICINGSTRATEGYID = TPF.PRICINGSTRATEGYID WHERE TPB.BERTHCODE=‘108211‘ AND TPF.FREEDATETYPE=‘1‘ SELECT LISTAGG( (SBPT.PARKSTIME||‘~‘||SBPT.PARKETIME),‘,‘) WITHIN GROUP (ORDER BY SBPT.PARKTIMEID) AS SAREE FROM SYS_BERTHANDTYPE SBT LEFT JOIN SYS_BERTHTYPEPARKTIME SBPT ON SBT.BERTHTYPEID = SBPT.BERTHTYPEID WHERE SBT.BERTHCODE=‘108211‘ AND SBPT.DATETYPE = 1 AND SBPT.ISALLOW =2 AS NOTALLOWTIMESPAN

20:00:00~23:59:59, 00:00:00~07:29:00

分号隔开‘;‘ 用trim实现去空格功能
select
  LISTAGG(SUBSTR(TO_CHAR(FREESTARTTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9)
      ||‘~‘||trim(SUBSTR(TO_CHAR(FREEENDTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9)),‘;‘)WITHIN GROUP (ORDER BY TPF.FREEID)
      AS  FREETIMESPAN 
             
      FROM TRA_PRICINGBERTH  TPB 
      LEFT JOIN TRA_PRICINGFREE TPF  ON TPB.PRICINGSTRATEGYID = TPF.PRICINGSTRATEGYID 
      WHERE 
      TPB.BERTHCODE=‘108211‘ 
      AND TPF.FREEDATETYPE=‘1‘  
      
      SELECT LISTAGG( (SBPT.PARKSTIME||‘~‘||SBPT.PARKETIME),‘;‘) WITHIN GROUP (ORDER BY SBPT.PARKTIMEID)
      AS SAREE  FROM SYS_BERTHANDTYPE SBT
      LEFT JOIN  SYS_BERTHTYPEPARKTIME SBPT ON SBT.BERTHTYPEID = SBPT.BERTHTYPEID 
      WHERE SBT.BERTHCODE=‘108211‘ AND  SBPT.DATETYPE = 1 AND  SBPT.ISALLOW =2
      AS NOTALLOWTIMESPAN

20:00:00~23:59:59;00:00:00~07:29:00

 

 

oracle日期合并 分别用逗号或者分号隔开

标签：