ORACLE 多列合并成一行数据 WM_CONCAT函数以及REPLACE
2024-10-19 04:55:57
WM_CONCAT()方法 注意字符长度
SELECT BERTHCODE,tpf.freedatetype,
( SELECT WM_CONCAT(SBPT.PARKSTIME||'~'||SBPT.PARKETIME) SAREE FROM
SYS_BERTHANDTYPE SBT LEFT JOIN SYS_BERTHTYPEPARKTIME SBPT
ON SBT.BERTHTYPEID = SBPT.BERTHTYPEID WHERE BERTHCODE='' AND
SBPT.DATETYPE = 1 AND SBPT.ISALLOW = 2)
NOTALLOWTIMESPAN, WM_CONCAT(SUBSTR(TO_CHAR(FREESTARTTIME,'YYYY-MM-DD HH24:MI:SS'),11,9)||'~'||SUBSTR(TO_CHAR(FREEENDTIME,'YYYY-MM-DD HH24:MI:SS'),11,9))
FREETIMESPAN
FROM TRA_PRICINGBERTH TPB LEFT JOIN TRA_PRICINGFREE TPF ON TPB.PRICINGSTRATEGYID = TPF.PRICINGSTRATEGYID
WHERE BERTHCODE='' AND FREEDATETYPE=1 GROUP BY BERTHCODE,tpf.freedatetype
order by BERTHCODE
可以使用以下这个方法REPLACE(WM_CONCAT())
SELECT TPB.BERTHCODE, (
SELECT REPLACE(WM_CONCAT(SBPT.PARKSTIME||'~'||SBPT.PARKETIME)OVER (PARTITION BY BERTHCODE,SBPT.PARKETIME), ',', ';')
AS SAREE FROM SYS_BERTHANDTYPE SBT
LEFT JOIN SYS_BERTHTYPEPARKTIME SBPT ON SBT.BERTHTYPEID = SBPT.BERTHTYPEID
WHERE SBT.BERTHCODE='' AND SBPT.DATETYPE = 1 AND SBPT.ISALLOW =2
) AS NOTALLOWTIMESPAN ,
REPLACE(WM_CONCAT(SUBSTR(TO_CHAR(FREESTARTTIME,'YYYY-MM-DD HH24:MI:SS'),11,9)
||'~'||SUBSTR(TO_CHAR(FREEENDTIME,'YYYY-MM-DD HH24:MI:SS'),11,9)), ',', ';')
AS FREETIMESPAN FROM TRA_PRICINGBERTH TPB
LEFT JOIN TRA_PRICINGFREE TPF ON TPB.PRICINGSTRATEGYID = TPF.PRICINGSTRATEGYID
WHERE
TPB.BERTHCODE=''
AND TPF.FREEDATETYPE=1
GROUP BY BERTHCODE
如果遇到版本问题 在Oracle11G下可以使用以下方法 LISTAGG()
SELECT TPB.BERTHCODE, ( 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='' AND SBPT.DATETYPE = 1 AND SBPT.ISALLOW =2
) AS NOTALLOWTIMESPAN ,
LISTAGG(SUBSTR(TO_CHAR(FREESTARTTIME,'YYYY-MM-DD HH24:MI:SS'),11,9)
||'~'||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=''
AND TPF.FREEDATETYPE=1
GROUP BY BERTHCODE
WM_CONCAT 合并成一行数据列 数据量大会超出字符长度
SELECT wm_concat(sbpt.ParkStime||'~'||sbpt.ParkEtime) FROM SYS_BerthTypeParkTime sbpt
REPLACE 分成多列
SELECT REPLACE(sbpt.ParkStime||'~'||sbpt.ParkEtime,',')
FROM SYS_BerthTypeParkTime sbpt
最新文章
- Oracle中较长number型数值的科学计数显示问题
- Android APP压力测试(一)之Monkey工具介绍
- STL---vector(向量)
- [CareerCup] 7.6 The Line Passes the Most Number of Points 经过最多点的直线
- Linux 中文乱码问题
- 如何使用批处理解决批量telnet命令的输入
- DG - logical standby switchover切换过程
- Mysql分区技术
- Spring Richclient — 企业级富客户端开发框架介绍,第 1 部分
- Spring 事务配置5种方式
- 浏览器 怪异模式(Quirks Mode) 与 标准模式(Standards Mode)
- zookeeper 之znode 节点
- JS复习:二十一章
- C#中saveFileDialog(另存为)保存图片文件
- WebStorm开发React项目,修代码之后运行的项目不更新
- YOLOv1
- 4.93Python数据类型之(8)集合
- scrapy 琐碎的东西
- MAC/Xcode简单操作命令
- plsql命令行窗口执行脚本打印输出