--存放重证评分的数据表
create table ZZPFapache2
(
ZZ_datetime DATE, --时间
ZZ_zongfen INTEGER, --总分
ZZ_shiwanglui INTEGER, --死亡率
ZZ_BINGRENID VARCHAR2(50), --病人ID
ZZ_h1f1 INTEGER, --第1行1个分
ZZ_h1m1 VARCHAR2(40),
ZZ_h1f2 INTEGER, --第1行2个分
ZZ_h1m2 VARCHAR2(40),
ZZ_h2f1 INTEGER, --第2行1个分
ZZ_h2m1 VARCHAR2(40),
ZZ_h2f2 INTEGER, --第2行2个分
ZZ_h2m2 VARCHAR2(40),
ZZ_h2f3 INTEGER, --第2行3个分
ZZ_h2m3 VARCHAR2(40),
ZZ_h3f1 INTEGER, --第3行1个分
ZZ_h3m1 VARCHAR2(40),
ZZ_h3f2 INTEGER, --第3行2个分
ZZ_h3m2 VARCHAR2(40),
ZZ_h3f3 INTEGER, --第3行3个分
ZZ_h3m3 VARCHAR2(40),
ZZ_h4f1 INTEGER, --第4行1个分
ZZ_h4m1 VARCHAR2(40),
ZZ_h4f2 INTEGER, --第4行2个分
ZZ_h4m2 VARCHAR2(40),
ZZ_h4f3 INTEGER, --第4行3个分
ZZ_h4m3 VARCHAR2(40),
ZZ_h5f1 INTEGER, --第5行1个分
ZZ_h5m1 VARCHAR2(40),
ZZ_h5f2 INTEGER, --第5行2个分
ZZ_h5m2 VARCHAR2(40),
ZZ_h5f3 INTEGER, --第5行3个分
ZZ_h5m3 VARCHAR2(40),
ZZ_h6f1 INTEGER, --第6行1个分
ZZ_h6m1 VARCHAR2(40),
ZZ_h6f2 INTEGER, --第6行2个分
ZZ_h6m2 VARCHAR2(40),
ZZ_h7f1 INTEGER, --第7行1个分
ZZ_h7m1 VARCHAR2(40),
ZZ_h7f2 INTEGER, --第6行2个分
ZZ_h7m2 VARCHAR2(40),
ZZ_h8f1 INTEGER, --第8行1个分
ZZ_h8m1 VARCHAR2(40),
ZZ_h8f2 INTEGER, --第8行2个分
ZZ_h8m2 VARCHAR2(40),
ZZ_f1 INTEGER, --复选第1个分
ZZ_f2 INTEGER, --复选第2个分
ZZ_f3 INTEGER, --复选第3个分
ZZ_f4 INTEGER, --复选第4个分
ZZ_f5 INTEGER, --复选第5个分
ZZ_GUserID VARCHAR2(40),
ZZ_GUserName VARCHAR2(40)
)
tablespace TS_IMIS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
--存放重症评分竖排数据视图
create or replace view zzpfdy as
select "ZZ_BINGRENID","ZZ_DATETIME","SUBJECT1","RESULT" from
(
select 1 line,ZZ_BINGRENID,ZZ_DATETIME , '得分' SUBJECT1, ZZ_ZONGFEN result from zzpfapache2
union all
select 2 line,ZZ_BINGRENID,ZZ_DATETIME , '死亡率'SUBJECT1, ZZ_SHIWANGLUI result from zzpfapache2
union all
select 3 line,ZZ_BINGRENID,ZZ_DATETIME , '年龄'SUBJECT1, ZZ_H1F1 result from zzpfapache2
union all
select 4 line,ZZ_BINGRENID,ZZ_DATETIME , '有严重慢性器官系统功能不全'SUBJECT1,ZZ_H1F2 result from zzpfapache2
union all
select 5 line,ZZ_BINGRENID,ZZ_DATETIME , '睁眼反应' SUBJECT1 ,ZZ_H2F1 result from zzpfapache2
union all
select 6 line,ZZ_BINGRENID,ZZ_DATETIME , '言语反应' SUBJECT1,ZZ_H2F2 result from zzpfapache2
union all
select 7 line,ZZ_BINGRENID,ZZ_DATETIME , '运动反应' SUBJECT1,ZZ_H2F3 result from zzpfapache2
union all
select 8 line,ZZ_BINGRENID,ZZ_DATETIME , '体温'SUBJECT1,ZZ_H3F1 result from zzpfapache2
union all
select 9 line,ZZ_BINGRENID,ZZ_DATETIME , '平均血压（mmHg）'SUBJECT1, ZZ_H3F2 result from zzpfapache2
union all
select 10 line,ZZ_BINGRENID,ZZ_DATETIME , '心率（次/分）'SUBJECT1 , ZZ_H3F3 result from zzpfapache2
union all
select 11 line,ZZ_BINGRENID,ZZ_DATETIME , '呼吸（次/分）'SUBJECT1 ,ZZ_H4F1 result from zzpfapache2
union all
select 12 line,ZZ_BINGRENID,ZZ_DATETIME , 'FIO2' SUBJECT1,ZZ_H4F2 result from zzpfapache2
union all
select 13 line,ZZ_BINGRENID,ZZ_DATETIME , '血清HCO3（无ABGs）（mmol/L）' SUBJECT1,ZZ_H4F3 result from zzpfapache2
union all
select 14 line,ZZ_BINGRENID,ZZ_DATETIME , '血清Na（mmol/L）' SUBJECT1,ZZ_H5F1 result from zzpfapache2
union all
select 15 line,ZZ_BINGRENID,ZZ_DATETIME , '血清K（mmol/L）' SUBJECT1,ZZ_H5F2 result from zzpfapache2
union all
select 16 line,ZZ_BINGRENID,ZZ_DATETIME , '动脉血PH' SUBJECT1 ,ZZ_H5F3 result from zzpfapache2
union all
select 17 line,ZZ_BINGRENID,ZZ_DATETIME , '血细胞比容（%）' SUBJECT1,ZZ_H6F1 result from zzpfapache2
union all
select 18 line,ZZ_BINGRENID,ZZ_DATETIME , 'WBC（*1000/mm3）' SUBJECT1,ZZ_H6F2 result from zzpfapache2
union all
select 19 line,ZZ_BINGRENID,ZZ_DATETIME , 'Pa02（mmHg...KPa）' SUBJECT1,ZZ_H7F1 result from zzpfapache2
union all
select 20 line,ZZ_BINGRENID,ZZ_DATETIME , 'A-aD02（mmHg...KPa）' SUBJECT1,ZZ_H7F2 result from zzpfapache2
union all
select 21 line,ZZ_BINGRENID,ZZ_DATETIME , '血清肌酐（mg/dl..micromol/l）' SUBJECT1,ZZ_H8F1 result from zzpfapache2
union all
select 22 line,ZZ_BINGRENID,ZZ_DATETIME , '急性肾功能衰竭' SUBJECT1,ZZ_H8F2 result from zzpfapache2
union all
select 23 line,ZZ_BINGRENID,ZZ_DATETIME , '有无功能不全免疫损害:肝' SUBJECT1,ZZ_F1 result from zzpfapache2
union all
select 24 line,ZZ_BINGRENID,ZZ_DATETIME , '有无功能不全免疫损害:心血管' SUBJECT1,ZZ_F2 result from zzpfapache2
union all
select 25 line,ZZ_BINGRENID,ZZ_DATETIME , '有无功能不全免疫损害:呼吸系统' SUBJECT1,ZZ_F3 result from zzpfapache2
union all
select 26 line,ZZ_BINGRENID,ZZ_DATETIME , '有无功能不全免疫损害:肾' SUBJECT1,ZZ_F4 result from zzpfapache2
union all
select 27 line,ZZ_BINGRENID,ZZ_DATETIME , '有无功能不全免疫损害:免疫抑制状态' SUBJECT1,ZZ_F5 result from zzpfapache2
) t
order by ZZ_DATETIME ,line;
--打印时为多列，所以又加入一个多列视图
CREATE OR REPLACE VIEW ZZPFDY2 AS
SELECT
a.subject1,
a.ZZ_DATETIME,a.n, A.ZZ_BINGRENID, A.result,
b.n bn, b.ZZ_BINGRENID bp,b.ZZ_DATETIME bt, B.result as result1,
c.n cn, c.ZZ_BINGRENID cp,c.ZZ_DATETIME ct, c.result as result2,
d.n dn, d.ZZ_BINGRENID dp,d.ZZ_DATETIME dt, d.result as result3,
e.n en, e.ZZ_BINGRENID ep,e.ZZ_DATETIME et, e.result as result4,
f.n fn, f.ZZ_BINGRENID fp,f.ZZ_DATETIME ft, f.result as result5
FROM
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME,subject1
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME,subject1 FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 0
) A,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result,ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 1
) B,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 2
) C,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 3
) D,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 4
) E,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 5
) F
WHERE A.N + 27 = B.N(+) and b.N + 27 = c.N(+) and
c.N + 27 = D.N(+) and D.N + 27 = E.N(+) and
e.n + 27 = f.N(+);
--竖排视图与打印视图的中间视图
create or replace view ZZPF as (select * from zzpfdy where ZZ_BINGRENID='1545187')