--存放重证评分的数据表
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\')