• oracle中行列转换,max,listagg使用


    需求:求门诊开甲功三项的病人的基本信息与化验结果的数据,

    自己的写法是:

    select a.sampleno,a.patientname,a.patientid ,listagg(testresult,',')
    within GROUP (order by testresult desc) abc
    from bslis.l_patientinfo a,bslis.l_testresult b where a.sampleno =b.sampleno and a.examinaim='甲功三项'
    group by a.sampleno ,a.patientname,a.patientid

    这上面是lis的检验结果

    lis中同一个样本号码对应三个testid然后是结果是分三条显示的,所以用了listagg然后就被动用了group by,所以打算lis与his信息union的话就比较难了

    =======================================

    select a.brid,mzhm,a.brxm,a.sfzh, xzz_qtdz, b.yz_z,b.yz_t, age,patientid from ms_brda a, ys_mz_jzls b ,l_lis_sqd c,ms_yj01 d
    where a.brid=b.brbh and a.mzhm=c.patientid and c.doctrequestno=d.sqdh and d.jzxh=b.jzxh
    and (b.yz_z is not null or b.yz_t is not null) and c.examinaim like '甲功三项' and c.bed_no is null

     这是his的病人基本信息,lis与his都有patientid,打算Union ,上面提到union很难实现

     =============================================================================

    select brid,mzhm,brxm,sfzh,csny, xzz_qtdz,yz_z,yz_t,age,patientid,
    max(FT3) FT3,max(FT4) FT4,max(TSH) TSH from (

    select a.brid,mzhm,a.brxm,a.sfzh,csny, xzz_qtdz, b.yz_z,b.yz_t, c.age,c.patientid,
    f.testresult,f.testid,
    case when f.testid = 1111 then f.testresult else '' end FT3,
    case when f.testid = 1112 then f.testresult else '' end FT4,
    case when f.testid = 1113 then f.testresult else '' end TSH
    from ms_brda a, ys_mz_jzls b ,l_lis_sqd c,ms_yj01 d ,
    bslis.l_jytmxx e,bslis.l_testresult f
    where a.brid=b.brbh and a.mzhm=c.patientid and
    c.doctrequestno=d.sqdh and d.jzxh=b.jzxh
    and (b.yz_z is not null or b.yz_t is not null) and
    c.examinaim like '甲功三项' and c.bed_no is null and
    e.sampleno = f.sampleno and c.doctrequestno = e.doctrequestno

    )t
    group by brid,mzhm,brxm,sfzh,csny, xzz_qtdz,yz_z,yz_t,age,patientid

    ========================

    上面的

    select a.brid,mzhm,a.brxm,a.sfzh,csny, xzz_qtdz, b.yz_z,b.yz_t, c.age,c.patientid, 
    f.testresult,f.testid,
    case when f.testid = 1111 then f.testresult else '' end FT3,
    case when f.testid = 1112 then f.testresult else '' end FT4,
    case when f.testid = 1113 then f.testresult else '' end TSH
    from ms_brda a, ys_mz_jzls b ,l_lis_sqd c,ms_yj01 d ,
    bslis.l_jytmxx e,bslis.l_testresult f
    where a.brid=b.brbh and a.mzhm=c.patientid and 
    c.doctrequestno=d.sqdh and d.jzxh=b.jzxh
    and (b.yz_z is not null or b.yz_t is not null) and 
    c.examinaim like '甲功三项' and c.bed_no is null and
    e.sampleno = f.sampleno and c.doctrequestno = e.doctrequestno 

     

  • 相关阅读:
    python学习 05 函数switch功能
    python学习 04 函数参数
    python学习 03 函数 (只会执行一次return就不会往下执行)
    【转】Jenkins+Ant+Jmeter接口自动化集成测试实例
    【转】python做一个http接口测试框架
    python学习 02 元组
    【转】使用 Python Mock 类进行单元测试
    【转】使用Python学习selenium测试工具
    【转】利用Python中的mock库对Python代码进行模拟测试
    【转】python测试开发面试题
  • 原文地址:https://www.cnblogs.com/thomasbc/p/9710029.html
Copyright © 2020-2023  润新知