create or replace view vw_zs_mzryzd_zydzbl as
select LSH000 ZYID00,
       (select ZDMC00
          from (select A.ZDMC00,A.ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+) and B.YXBZ00='1'
                   and B.ZDBM00='ZDLB00' and B.VALUE0='入院诊断' order by A.PLXH00 asc)
         where ZYID00(+)=C.LSH000 and rownum=1) RYZD00, --入院诊断
       (select ZDMC00
          from (select A.ZDMC00,A.ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+)  and B.YXBZ00='1'
                   and B.ZDBM00='ZDLB00' and B.VALUE0='门诊诊断' order by A.PLXH00 asc)
         where ZYID00(+)=C.LSH000 and rownum=1) MZZD00, --门(急)诊诊断
       (select ZDM000
         from (select A.ZDM000,A.ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+)  and B.YXBZ00='1'
                   and B.ZDBM00='ZDLB00' and B.VALUE0='门诊诊断' order by A.PLXH00 asc)
         where ZYID00(+)=C.LSH000 and rownum=1) MZZDBM, --门(急)诊诊断编号
       (select ZDMC00
          from (select A.ZDMC00,A.ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+) and B.YXBZ00='1'
                   and B.ZDBM00='ZDLB00' and B.VALUE0='病理诊断' order by A.PLXH00 asc)
         where ZYID00(+)=C.LSH000 and rownum=1) BLZD00, --病理诊断
       (select ZDMC00
          from (select A.ZDMC00,A.ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+) and A.ZDFS00='0'  and B.YXBZ00='1'
                  and B.ZDBM00='ZYZDLB' and B.VALUE0='入院疾病诊断' order by A.PLXH00 asc)
         where ZYID00(+)=C.LSH000 and rownum=1) ZYRYZD, --中医入院诊断
       (select ZDMC00
          from (select A.ZDMC00,ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+) and B.YXBZ00='1'  and A.ZDFS00='0'
                   and B.ZDBM00='ZYZDLB' and B.VALUE0='门诊疾病诊断' order by A.PLXH00 asc)
         where ZYID00(+)=C.LSH000 and rownum=1) ZYMZZD, --中医门(急)诊诊断
       (select ZDM000
          from (select A.ZDM000,ZYID00 from BQ_BRZDXX A, XT_ZD0000 B
                 where A.ZDLB00=B.DM0000(+) and B.YXBZ00='1'  and A.ZDFS00='0'
                   and B.ZDBM00='ZYZDLB' and B.VALUE0='门诊疾病诊断' order by A.PLXH00 asc)
          where ZYID00(+)=C.LSH000 and rownum=1) ZYZDBM --中医门(急)诊诊断编码
from  BA_BRZYXX C;