SQL 占位符 和 union all

--------------药品处方明细2--------------------------------------

select 
Max(PTMax)  as 普通处方金额最大值,
Min(PTMin)  as 普通处方金额最小值,
Avg(PTAvg)  as 普通处方张数平均值,

Max(JSYMax)  as 精神一处方金额最大值,
Min(JSYMin)  as 精神一处方金额最小值,
Avg(JSYAvg)  as 精神一处方张数平均值,
    
Max(JSEMax)  as 精神二处方金额最大值,
Min(JSEMin)  as 精神二处方金额最小值,
Avg(JSEAvg)  as 精神二处方张数平均值,
    
Max(MZMax)  as 麻醉处方金额最大值,
Min(MZMin)  as 麻醉处方金额最小值,
Avg(MZAvg)  as 麻醉处方张数平均值,
    
Max(DXMax)  as 毒性处方金额最大值,
Min(DXMin)  as 毒性处方金额最小值,
Avg(DXAvg)  as 毒性处方张数平均值
from


(

select 
    Max(t.TC_OD_Sum) as PTMax ,
    Min(t.TC_OD_Sum) as PTMin ,
    Avg(t.TC_OD_Sum) as PTAvg ,
    
    0 as JSYMax,
    0 as JSYMin,
    0 as JSYAvg,
    
    0 as JSEMax,
    0 as JSEMin,
    0 as JSEAvg,
    
    0 as MZMax,
    0 as MZMin,
    0 as MZAvg,
    
    0 as DXMax,
    0 as DXMin,
    0 as DXAvg
    from  TB_OD_PRESCRIPTION t
    where  exists
    (
    select op.TC_RX_NO  from  TB_OD_PRESCRIPTION op
    join tb_dic_drug g on op.TC_OD_DG_ID=g.tc_dg_id
    left join Tb_Dic_Drug_Character ddc on g.tc_Ci_Id=ddc.tc_Ci_Id where  ddc.Tc_Dc_Toxicologic=‘普通药‘ and  T.TC_RX_NO=op.TC_RX_NO
    )  
union all                   
    
    select 
    null,
    null,
    null,
    Max(t.TC_OD_Sum) as JSYMax ,
    Min(t.TC_OD_Sum) as JSYMin ,
    Avg(t.TC_OD_Sum) as JSYAvg ,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null
    from  TB_OD_PRESCRIPTION t
    where  exists
    (
    select op.TC_RX_NO  from  TB_OD_PRESCRIPTION op
    join tb_dic_drug g on op.TC_OD_DG_ID=g.tc_dg_id
    left join Tb_Dic_Drug_Character ddc on g.tc_Ci_Id=ddc.tc_Ci_Id where  ddc.Tc_Dc_Toxicologic=‘精神I类‘ and  T.TC_RX_NO=op.TC_RX_NO
    )
    union all                   
    
    select 
    null,
    null,
    null,
    null,
    null,
    null,
    Max(t.TC_OD_Sum) as JSEMax ,
    Min(t.TC_OD_Sum) as JSEMin ,
    Avg(t.TC_OD_Sum) as JSEAvg ,
    null,
    null,
    null,
    null,
    null,
    null
    from  TB_OD_PRESCRIPTION t
    where  exists
    (
    select op.TC_RX_NO  from  TB_OD_PRESCRIPTION op
    join tb_dic_drug g on op.TC_OD_DG_ID=g.tc_dg_id
    left join Tb_Dic_Drug_Character ddc on g.tc_Ci_Id=ddc.tc_Ci_Id where  ddc.Tc_Dc_Toxicologic=‘精神II类‘ and  T.TC_RX_NO=op.TC_RX_NO
    )                     
    union all   
    select 
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    Max(t.TC_OD_Sum) as  MZMax,
    Min(t.TC_OD_Sum) as  MZMin,
    Avg(t.TC_OD_Sum) as  MZAvg,
    null,
    null,
    null
    from  TB_OD_PRESCRIPTION t
    where  exists
    (
    select op.TC_RX_NO  from  TB_OD_PRESCRIPTION op
    join tb_dic_drug g on op.TC_OD_DG_ID=g.tc_dg_id
    left join Tb_Dic_Drug_Character ddc on g.tc_Ci_Id=ddc.tc_Ci_Id where  ddc.Tc_Dc_Toxicologic=‘麻醉药‘ and  T.TC_RX_NO=op.TC_RX_NO
    )     
     union all   
    select 
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    Max(t.TC_OD_Sum) as DXMax,
    Min(t.TC_OD_Sum) as DXMin,
    Avg(t.TC_OD_Sum) as DXAvg
    from  TB_OD_PRESCRIPTION t
    where  exists
    (
    select op.TC_RX_NO  from  TB_OD_PRESCRIPTION op
    join tb_dic_drug g on op.TC_OD_DG_ID=g.tc_dg_id
    left join Tb_Dic_Drug_Character ddc on g.tc_Ci_Id=ddc.tc_Ci_Id where  ddc.Tc_Dc_Toxicologic=‘毒性药‘ and  T.TC_RX_NO=op.TC_RX_NO
    )    
    ）                                            

SQL 占位符 和 union all

标签：size   col   alt   mamicode   color   left join   区别   最小值   logic