oracle wm_concat函数简单实例(实现行转成用分隔符分隔的一列)
来源:互联网 发布:js 数组遍历运行不同步 编辑:程序博客网 时间:2024/05/16 00:51
主表:故障单表 SP_PD_FAULT
从表:报障单表 SP_PD_FAULT_REPORT
目的:通过故障单关联查询出该故障单的报障单号,且用逗号分隔显示为一列
SELECT A.*, B.FAULT_NOS, B.FAULT_REPORT_IDS FROM (SELECT * FROM (SELECT DISTINCT (F.ID) F_ID, F.*, O.FAULT_OCCUR_TIME, O.FAULT_ADDRESS, O.FAULT_DESC FROM LCAM_SC.SP_PD_FAULT F, (SELECT A.FAULT_ID, A.FEEDER_ID, A.PROVINCE_CODE, A.FAULT_REPORT_STATUS, A.FAULT_REPORT_FROM, A.FAULT_REPORT_NO, A.FAULT_ADDRESS, A.FEEDER_NAME, A.FAULT_REPORT_STATUS, A.SUBSTATION_ID, A.SUBSTATION_NAME, A.ORGANIZATION_ID, A.TEAM_ID, A.BUREAU_CODE, A.CS_WORKORDER_NO, A.FAULT_DESC, A.FAULT_REPORTER, B.FAULT_OCCUR_TIME FROM LCAM_SC.SP_PD_FAULT_REPORT A, (SELECT FAULT_ID, MIN(FAULT_OCCUR_TIME) FAULT_OCCUR_TIME FROM LCAM_SC.SP_PD_FAULT_REPORT GROUP BY FAULT_ID) B WHERE A.FAULT_ID = B.FAULT_ID AND (A.IS_MERGE = '1' OR A.IS_MERGE IS NULL) AND A.PROVINCE_CODE = '03' AND B.FAULT_OCCUR_TIME BETWEEN TO_DATE('2016-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND (TO_DATE('2017-08-02 00:00', 'yyyy-mm-dd hh24:mi'))) O WHERE F.ID = O.FAULT_ID AND F.PROVINCE_CODE IS NOT NULL AND F.BUREAU_CODE IS NOT NULL AND O.PROVINCE_CODE = '03' AND F.FAULT_STATUS IN (2, 3, 4, 5, 6, 7) AND O.FAULT_OCCUR_TIME BETWEEN TO_DATE('2016-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND TO_DATE('2017-08-02 00:00', 'yyyy-mm-dd hh24:mi'))) A, (SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO)) FAULT_NOS, TO_CHAR(WM_CONCAT(ID)) FAULT_REPORT_IDS, FAULT_ID FROM LCAM_SC.SP_PD_FAULT_REPORT GROUP BY FAULT_ID) B WHERE A.ID = B.FAULT_ID(+)
从表使用GROUP BY外键字段,通过WM_CONCAT函数把报账单号和ID连接成一个字符串
(SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO)) FAULT_NOS,
TO_CHAR(WM_CONCAT(ID)) FAULT_REPORT_IDS,
FAULT_ID
FROM LCAM_SC.SP_PD_FAULT_REPORT
GROUP BY FAULT_ID) B
WHERE A.ID = B.FAULT_ID(+)
但是:报错 不存在的LOB值
出现这个错误很多时候是使用了wmsys.wm_concat的同时使用了group ,distinct 或者union,本来两者没有问题,问题在于:
1.Oracle Database 10g Enterprise Edition Release 10.2.0.5.0以后的版本wmsys.wm_concat查询出的是LOB类型
2.oralce的SQL语句中若查询了LOB字段是不能使用distinct,union,和group by等关键字的。
改成在把关联查询写成子查询
SELECT A.*, (SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO)) FROM LCAM_SC.SP_PD_FAULT_REPORT B WHERE A.ID = B.FAULT_ID) FAULT_NOS, (SELECT TO_CHAR(WM_CONCAT(ID)) FROM LCAM_SC.SP_PD_FAULT_REPORT B WHERE A.ID = B.FAULT_ID) FAULT_REPORT_IDS FROM (SELECT DISTINCT (F.ID) F_ID, F.*, O.FAULT_OCCUR_TIME, O.FAULT_ADDRESS, O.FAULT_DESC FROM LCAM_SC.SP_PD_FAULT F, (SELECT A.FAULT_ID, A.FEEDER_ID, A.PROVINCE_CODE, A.FAULT_REPORT_STATUS, A.FAULT_REPORT_FROM, A.FAULT_REPORT_NO, A.FAULT_ADDRESS, A.FEEDER_NAME, A.FAULT_REPORT_STATUS, A.SUBSTATION_ID, A.SUBSTATION_NAME, A.ORGANIZATION_ID, A.TEAM_ID, A.BUREAU_CODE, A.CS_WORKORDER_NO, A.FAULT_DESC, A.FAULT_REPORTER, B.FAULT_OCCUR_TIME FROM LCAM_SC.SP_PD_FAULT_REPORT A, (SELECT FAULT_ID, MIN(FAULT_OCCUR_TIME) FAULT_OCCUR_TIME FROM LCAM_SC.SP_PD_FAULT_REPORT GROUP BY FAULT_ID) B WHERE A.FAULT_ID = B.FAULT_ID AND (A.IS_MERGE = '1' OR A.IS_MERGE IS NULL) AND A.PROVINCE_CODE = '03' AND B.FAULT_OCCUR_TIME BETWEEN TO_DATE('2016-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND (TO_DATE('2017-08-02 00:00', 'yyyy-mm-dd hh24:mi'))) O WHERE F.ID = O.FAULT_ID AND F.PROVINCE_CODE IS NOT NULL AND F.BUREAU_CODE IS NOT NULL AND O.PROVINCE_CODE = '03' AND F.FAULT_STATUS IN (2, 3, 4, 5, 6, 7) AND O.FAULT_OCCUR_TIME BETWEEN TO_DATE('2016-01-01 00:00', 'yyyy-mm-dd hh24:mi') AND TO_DATE('2017-08-02 00:00', 'yyyy-mm-dd hh24:mi')) A
外面包一层
SELECT A.*,
(SELECT TO_CHAR(WM_CONCAT(FAULT_REPORT_NO))
FROM LCAM_SC.SP_PD_FAULT_REPORT B
WHERE A.ID = B.FAULT_ID) FAULT_NOS,
(SELECT TO_CHAR(WM_CONCAT(ID))
FROM LCAM_SC.SP_PD_FAULT_REPORT B
WHERE A.ID = B.FAULT_ID) FAULT_REPORT_IDS
FROM (
把需要的连接字符串使用子查询查出来
- oracle wm_concat函数简单实例(实现行转成用分隔符分隔的一列)
- 重写Oracle的wm_concat函数,以自定义分隔符、排序
- Oracle wmsys.wm_concat()函数 顺序问题 分隔符问题
- oracle中有分隔符的列转成多行
- oracle wm_concat函数,用于列转行,逗号分隔
- oracle wm_concat函数,用于列转行,逗号分隔
- oracle wm_concat函数,用于列转行,逗号分隔
- oracle wm_concat函数,用于列转行,逗号分隔
- oracle把一列数据变为一个字段用逗号分隔
- oracle存储过程实现的split函数,分隔字符串
- oracle存储过程实现的split函数,分隔字符串
- sql server函数实现把分隔字符串转成表格
- kettle分隔列转成行
- 把用","分隔的字符串转化成Table的一列
- 分隔符分隔的字符串
- WM_CONCAT LISTAGG函数,ORACLE列转行聚合的简单实现
- Oracle函数,按分隔符截取字符串
- 实现一个以空白符分隔字符串的split函数
- 最简单的IOS Block与协议
- TASKCTL4.5(安装)单机部署
- 暑期学习记录10
- Shiro CAS对接原理一览
- Mybatis中xml判断字符串不为空和null简单方法
- oracle wm_concat函数简单实例(实现行转成用分隔符分隔的一列)
- 查找:B+树
- Python面试题
- UVA 12493 Stars(扩展欧几里德+欧拉函数)
- 职场人士必看:HR不可公开的真相
- 用记事本写java的第一个程序hello world 以及eclipse的使用方法
- ajax上传文件后台获取不到问题
- 关于比特币 https://www.zhihu.com/question/22076666
- 随笔