select        a.bs_sn,        a.bs_bd_no,        a.bs_bk_code,        a.bs_kind_no,        a.bs_flag,        b.det_flag,        c.bp_in_no,        c.bp_name,        c.bp_sex,        c.bp_age,        c.bp_age_unit,        c.bp_dept,        c.bp_bed_no,        d.inf_date,        d.inf_type,        e.sta_date,        e.sta_user                  from bd_stock as a                 left join (select row_number() over (partition by det_bs_sn order by det_flag DESC) as rownum,det_bs_sn,det_id,det_flag from dbo.bd_match_detail where det_flag!=-1) as b on a.bs_sn=b.det_bs_sn and b.rownum=1                left join bd_patients as c on b.det_id=c.bp_id                left join (select row_number() over (partition by inf_bp_id,inf_bd_sn order by inf_date DESC) as rownum,inf_bp_id,inf_bd_sn,inf_type,inf_date from bd_infusion where inf_type = 1) as d on b.det_id = d.inf_bp_id and b.det_bs_sn=d.inf_bd_sn and d.rownum=1                left join (select row_number() over (partition by sta_code,sta_sta_code order by sta_date DESC) as rownum,sta_code,sta_sta_code,sta_user,sta_date from dbo.bd_status where sta_sta_code=‘10600‘) as e on a.bs_bd_no=e.sta_code                     where b.det_flag=6  and d.inf_date>=‘2013-12-02‘ and d.inf_date<=‘2014-12-02‘