应用场景:主表与从表一对多的关系.一个患者有多个随访记录,我要直观的显示这个患者都有哪写随访记录.
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