首页 新闻 搜索 专区 学院

Oracle 如何返回拼接字符串?

0
悬赏园豆:10 [已解决问题] 解决于 2016-06-17 15:51

select case vital_signs
when '体温' then
to_char(VITAL_SIGNS_VALUES) + '|' + NOTE
else
null
end as tt
from VITAL_SIGNS_REC

 

查询VITAL_SIGNS_REC

如果vital_signs是 体温 则返回 VITAL_SIGNS_VALUES + '|' + NOTE

VITAL_SIGNS_VALUES 是数字哦。

 

完整的sql是这样的:


SELECT T.WARD_CODE,
T.PATIENT_ID,
T.VISIT_ID,
T.BED_NO,
T.BED_LABEL,
T.NAME,
T.SEX,
T.NURSING_CLASS,
cast(V.BT as VARCHAR(10)) as BT,
cast(V.PBT as VARCHAR(10)) as PBT,
cast(V.BR as VARCHAR(10)) as BR,
cast(V.P as VARCHAR(10)) as P,
cast(V.BP_H as VARCHAR(10)) as BP_H,
cast(V.BP_L as VARCHAR(10)) as BP_L,
cast(V2.WT as VARCHAR(10)) as WT,
cast(V3.HT as VARCHAR(10)) as HT,
cast(V.DBC as VARCHAR(10)) as DBC,
cast(V.BS as VARCHAR(10)) as BS,
cast(V.BO as VARCHAR(10)) as BO,
cast(V.HR as VARCHAR(10)) as HR
FROM SINLDO.PATS_IN_HOSIPTAL T
LEFT JOIN (SELECT PATIENT_ID,
VISIT_ID,
TIME_POINT,
(CASE VITAL_SIGNS
WHEN '体温' THEN
TO_CHAR(VITAL_SIGNS_VALUES) || '|' || NOTE
ELSE
null
END) AS BT,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '物理降温' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS PBT,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '呼吸' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS BR,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '脉搏' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS P,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血压HIGH' THEN
VITAL_SIGNS_VALUES
WHEN '血压high' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS BP_H,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血压LOW' THEN
VITAL_SIGNS_VALUES
WHEN '血压Low' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS BP_L,
'' AS DBC,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血糖' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS BS,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血氧' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS BO,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '心率' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS HR,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '动脉压' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS ABP,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '神志' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS MIND,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '左瞳孔(大小)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS L_PU_SZ,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '右瞳孔(大小)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS R_PU_SZ,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '左瞳孔(光)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS L_PU_LT,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '右瞳孔(光)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS R_PU_LT,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '箱温' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS BOX_T,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '疼痛强度' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS PAIN
FROM SINLDO.VITAL_SIGNS_REC
WHERE TIME_POINT =
TO_DATE('2016-06-15 02:00:00',
'YYYY-MM-DD HH24:MI:SS')
GROUP BY PATIENT_ID, VISIT_ID, TIME_POINT) V ON V.PATIENT_ID =
T.PATIENT_ID
AND V.VISIT_ID =
T.VISIT_ID
LEFT JOIN (SELECT PATIENT_ID,
VISIT_ID,
TIME_POINT,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '体重' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS WT
FROM VITAL_SIGNS_REC T
WHERE VITAL_SIGNS IS NOT NULL
AND T.TIME_POINT =
TO_DATE('2016-06-15 14:00:00',
'YYYY-MM-DD HH24:mi:ss')
GROUP BY PATIENT_ID, VISIT_ID, TIME_POINT) V2 ON V2.PATIENT_ID =
T.PATIENT_ID
AND V2.VISIT_ID =
T.VISIT_ID
LEFT JOIN (SELECT PATIENT_ID,
VISIT_ID,
TIME_POINT,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '身高' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS HT
FROM VITAL_SIGNS_REC T
WHERE VITAL_SIGNS IS NOT NULL
AND T.TIME_POINT =
TO_DATE('2016-06-15 08:00:00',
'YYYY-MM-DD HH24:mi:ss')
GROUP BY PATIENT_ID, VISIT_ID, TIME_POINT) V3 ON V3.PATIENT_ID =
T.PATIENT_ID
AND V3.VISIT_ID =
T.VISIT_ID
WHERE T.NAME IS NOT NULL
AND T.WARD_CODE = '9020'
ORDER BY T.BED_NO

Eysa的主页 Eysa | 初学一级 | 园豆:70
提问于:2016-06-15 13:06
< >
分享
最佳答案
0
SELECT T.WARD_CODE,
T.PATIENT_ID,
T.VISIT_ID,
T.BED_NO,
T.BED_LABEL,
T.NAME,
T.SEX,
T.NURSING_CLASS,
cast(V.BT as VARCHAR(10)) as BT,
cast(V.PBT as VARCHAR(10)) as PBT,
cast(V.BR as VARCHAR(10)) as BR,
cast(V.P as VARCHAR(10)) as P,
cast(V.BP_H as VARCHAR(10)) as BP_H,
cast(V.BP_L as VARCHAR(10)) as BP_L,
cast(V2.WT as VARCHAR(10)) as WT,
cast(V3.HT as VARCHAR(10)) as HT,
cast(V.DBC as VARCHAR(10)) as DBC,
cast(V.BS as VARCHAR(10)) as BS,
cast(V.BO as VARCHAR(10)) as BO,
cast(V.HR as VARCHAR(10)) as HR
FROM SINLDO.PATS_IN_HOSIPTAL T
LEFT JOIN (SELECT PATIENT_ID,
VISIT_ID,
TIME_POINT,
(CASE VITAL_SIGNS
WHEN '体温' THEN
TO_CHAR(VITAL_SIGNS_VALUES) || '|' || NOTE
ELSE
null
END) ||
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '物理降温' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '呼吸' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '脉搏' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血压HIGH' THEN
VITAL_SIGNS_VALUES
WHEN '血压high' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血压LOW' THEN
VITAL_SIGNS_VALUES
WHEN '血压Low' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
'' AS DBC,
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血糖' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '血氧' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '心率' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '动脉压' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '神志' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '左瞳孔(大小)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '右瞳孔(大小)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '左瞳孔(光)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '右瞳孔(光)' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '箱温' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) ||
TO_CHAR(SUM((CASE UPPER(VITAL_SIGNS)
WHEN '疼痛强度' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) 
FROM SINLDO.VITAL_SIGNS_REC
WHERE TIME_POINT =
TO_DATE('2016-06-15 02:00:00',
'YYYY-MM-DD HH24:MI:SS')
GROUP BY PATIENT_ID, VISIT_ID, TIME_POINT) V ON V.PATIENT_ID =
T.PATIENT_ID
AND V.VISIT_ID =
T.VISIT_ID
LEFT JOIN (SELECT PATIENT_ID,
VISIT_ID,
TIME_POINT,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '体重' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS WT
FROM VITAL_SIGNS_REC T
WHERE VITAL_SIGNS IS NOT NULL
AND T.TIME_POINT =
TO_DATE('2016-06-15 14:00:00',
'YYYY-MM-DD HH24:mi:ss')
GROUP BY PATIENT_ID, VISIT_ID, TIME_POINT) V2 ON V2.PATIENT_ID =
T.PATIENT_ID
AND V2.VISIT_ID =
T.VISIT_ID
LEFT JOIN (SELECT PATIENT_ID,
VISIT_ID,
TIME_POINT,
TO_CHAR(SUM((CASE VITAL_SIGNS
WHEN '身高' THEN
VITAL_SIGNS_VALUES
ELSE
null
END))) AS HT
FROM VITAL_SIGNS_REC T
WHERE VITAL_SIGNS IS NOT NULL
AND T.TIME_POINT =
TO_DATE('2016-06-15 08:00:00',
'YYYY-MM-DD HH24:mi:ss')
GROUP BY PATIENT_ID, VISIT_ID, TIME_POINT) V3 ON V3.PATIENT_ID =
T.PATIENT_ID
AND V3.VISIT_ID =
T.VISIT_ID
WHERE T.NAME IS NOT NULL
AND T.WARD_CODE = '9020'
ORDER BY T.BED_NO

 

收获园豆:10
刘宏玺 | 专家六级 |园豆:14004 | 2016-06-15 14:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册