oracle 行转列-WMSYS. wm_contact的运用
来源:互联网 发布:极坐标 经纬度 java 编辑:程序博客网 时间:2024/06/05 02:54
应用场景:主表与从表一对多的关系.一个患者有多个随访记录,我要直观的显示这个患者都有哪写随访记录.
sql:
SELECT WMSYS.WM_CONCAT(id) AS id ,
CUSTOMER_MAIN_ID,
MAX(CUSTOMER_DETAIL_ID) AS CUSTOMER_DETAIL_ID ,
TO_CHAR(START_TIME,'yyyy-mm-dd') AS START_TIME ,
WMSYS.WM_CONCAT(TO_CHAR(ITEM_NAME)) AS ITEM_NAME,
MAX(customer_name) AS customer_name ,
MAX(sex) AS sex,
MAX(age) AS age,
MAX(client_id) AS client_id,
WMSYS.WM_CONCAT(type) AS type,
WMSYS.WM_CONCAT(item_id) AS plan_item_id
FROM
(SELECT pd.PLAN_DETAIL_ID AS id,
pd.CUSTOMER_MAIN_ID ,
pd.CUSTOMER_DETAIL_ID ,
pd.plan_time AS START_TIME,
pi.ITEM_NAME,
pi.ITEM_ID,
cm.customer_name,
cm.sex,
to_char(sysdate,'yyyy')-to_char(cm.BIRTHDAY,'yyyy')+decode(sign(to_char(sysdate,'mmdd')-to_char(cm.BIRTHDAY,'mmdd')),-1,-1,0,0,1,0) as age,
cm.client_id,
'1' AS type
FROM HBPMS.HBP_PLAN_DETAILpd
inner JOIN (select hcm.customer_name,hcm.sex,hcm.HBP_CUSTOMER_MAIN_ID,hcm.BIRTHDAY,hcm.client_id from HBPMS.HBP_CUSTOMER_MAINhcm
left join HBPMS.sys_userssu on hcm.ORGANIZATION_ID=su.ORGANIZATION_ID
left join HBPMS.sys_user_mappingsuma on su.user_id = suma.user_id
where suma.O2O_DOCTOR_ID=#{doctorId})cm
ON pd.CUSTOMER_MAIN_ID = cm.HBP_CUSTOMER_MAIN_ID
LEFT JOIN HBPMS.DICT_PLAN_ITEM pi
ON pd.PLAN_ITEM_ID = pi.ITEM_ID
WHERE pd.PLAN_STATUS=1 and<![CDATA[ pd.plan_time <= sysdate+5]]>
UNION ALL
SELECT ht.TASK_ID AS id,
ht.CUSTOMER_MAIN_ID ,
ht.CUSTOMER_DETAIL_ID ,
ht.TASK_TIME AS START_TIME,
pi.ITEM_NAME,
pi.ITEM_ID,
cm.customer_name,
cm.sex,
to_char(sysdate,'yyyy')-to_char(cm.BIRTHDAY,'yyyy')+decode(sign(to_char(sysdate,'mmdd')-to_char(cm.BIRTHDAY,'mmdd')),-1,-1,0,0,1,0) as age,
cm.client_id,
'2' AS type
FROM HBPMS.HBP_TASKht
inner JOIN (select hcm.customer_name,hcm.sex,hcm.HBP_CUSTOMER_MAIN_ID,hcm.BIRTHDAY,hcm.client_id from HBPMS.HBP_CUSTOMER_MAINhcm
left join HBPMS.sys_userssu on hcm.ORGANIZATION_ID=su.ORGANIZATION_ID
left join HBPMS.sys_user_mappingsuma on su.user_id = suma.user_id
where suma.O2O_DOCTOR_ID=#{doctorId})cm
ON ht.CUSTOMER_MAIN_ID = cm.HBP_CUSTOMER_MAIN_ID
LEFT JOIN HBPMS.DICT_PLAN_ITEM pi
ON ht.ITEM_ID = pi.ITEM_ID
WHERE TASK_STATUS=1
) t
GROUP BY t.CUSTOMER_MAIN_ID ,
t.START_TIME
ORDER BY t.START_TIMEasc
- oracle 行转列-WMSYS. wm_contact的运用
- Oracle行转列的方法—wmsys.wm_concat
- replace wm_contact的问题
- Oracle 11g 重建WMSYS用户的WMSYS.WM_CONCAT函数
- 重写oracle wm_contact函数脚本
- oracle 的wmsys.wm_concat函数用法
- oracle 的wmsys.wm_concat函数用法
- Oracle函数wmsys.wm_concat的使用
- Oracle函数wmsys.wm_concat的使用
- oracle wmsys.wm_concat(column)函数的使用
- oracle wmsys.wm_concat 函数的使用
- Oracle函数wmsys.wm_concat的使用
- ORACLE的WMSYS.WM_CONCAT聚合函数
- Oracle wmsys.wm_concat的几个用法
- oracle 替换wmsys.wm_concat的函数
- Oracle 函数 wmsys.wm_concat 的几个用法
- oracle 函数WMSYS.WM_CONCAT()的用法
- Oracle行转列函数WMSYS.WM_CONCAT() 和 Listagg()
- Doc12(innerText与innerHTML及兼容性写法)
- RFS( 二 )----一些常用的快捷键
- Leetcode Best Time to Buy and Sell Stock with Cooldown
- php爬虫:知乎用户数据爬取和分析
- 一万小时定律
- oracle 行转列-WMSYS. wm_contact的运用
- Docker学习笔记(一)
- Python 科学计算
- ROS学习——入门篇(1)学习步骤
- LruCache
- iOS开发笔记--iOS应用架构谈 开篇
- Java WebService 简单实例
- ExecutorService 线程池
- 详细介绍QT 主要类使用方法