根据部门及医生分组查询

 

 

select 
    t0.deptName, 
    t0.doctorName,
    IFNULL(t0.num,0) as num0,
    IFNULL(t1.num,0) as num1,
    IFNULL(t2.num,0) as num2,
    IFNULL(t3.num,0) as num3,
    IFNULL(t4.num,0) as num4,
    IFNULL(t5.num,0) as num5,
    IFNULL(t6.num,0) as num6,
    IFNULL(t7.num,0) as num7,
    IFNULL(t8.num,0) as num8,
    IFNULL(t9.num,0) as num9,
    IFNULL(t10.num,0) as num10,
    IFNULL(t11.num,0) as num11,
    IFNULL(t13.num,0) as num13,
    IFNULL(t10.num,0) as num14,
    IFNULL(t11.num,0) as num15,
    IFNULL(t13.num,0) as num16,
    IFNULL(t10.num,0) as num17,
    IFNULL(t13.num,0) as num18,
    IFNULL(t10.num,0) as num19

from 
-- ------------
-- 总挂号数
-- ------------
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    (
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.appointment_state,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
    ) t
    group by t.deptId,t.doctorId
) t0

-- ------------
-- 退号
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.appointment_state = -1
    ) t 
    group by t.deptId,t.doctorId
) t1
on t0.deptId = t1.deptId and t0.doctorId = t1.doctorId

-- ------------
-- 实际挂号数   =    初诊数 + 复诊数 + 转诊数
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit in (0,1,2)
    ) t 
    group by t.deptId,t.doctorId
) t2
on t0.deptId = t2.deptId and t0.doctorId = t2.doctorId

-- ------------
-- 指定
-- ------------    
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.is_appoint_resource = 1
    ) t 
    group by t.deptId,t.doctorId
) t3
on t0.deptId = t3.deptId and t0.doctorId = t3.doctorId


-- ------------
-- 初诊
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 0
    ) t 
    group by t.deptId,t.doctorId
) t4
on t0.deptId = t4.deptId and t0.doctorId = t4.doctorId

-- ------------
-- 复诊
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 1
    ) t 
    group by t.deptId,t.doctorId
) t5
on t0.deptId = t5.deptId and t0.doctorId = t5.doctorId



-- ------------
-- 复诊
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 4
    ) t 
    group by t.deptId,t.doctorId
) t6
on t0.deptId = t6.deptId and t0.doctorId = t6.doctorId

-- ------------
-- 团队
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 7
    ) t 
    group by t.deptId,t.doctorId
) t7
on t0.deptId = t7.deptId and t0.doctorId = t7.doctorId


-- ------------
-- 简易 
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 5
    ) t 
    group by t.deptId,t.doctorId
) t8
on t0.deptId = t8.deptId and t0.doctorId = t8.doctorId



-- ------------
-- 转诊
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 2
    ) t 
    group by t.deptId,t.doctorId
) t9
on t0.deptId = t9.deptId and t0.doctorId = t9.doctorId

-- ------------
-- 疫苗
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.subsequent_visit = 2
    ) t 
    group by t.deptId,t.doctorId
) t10
on t0.deptId = t10.deptId and t0.doctorId = t10.doctorId

-- ------------
-- 微信
-- ------------
left join 
(
    select count(1) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.data_source = 2
    ) t 
    group by t.deptId,t.doctorId
) t11
on t0.deptId = t11.deptId and t0.doctorId = t11.doctorId

-- ------------
-- 挂号费 t12
-- ------------



-- ------------
-- 急诊
-- ------------
left join 
(
    select round(sum(t.realFee),2) as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where e.`itemCode` = 'jiajifei'    -- 加急费：急诊费
    ) t 
    group by t.deptId,t.doctorId
) t13
on t0.deptId = t13.deptId and t0.doctorId = t13.doctorId

-- ------------
-- 工本费 t14
-- ------------
left join 
(
    select round(sum(t.realFee),2)  as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where e.`itemCode` = 'bingliben'    -- 病历本：工本费
    ) t 
    group by t.deptId,t.doctorId
) t14
on t0.deptId = t14.deptId and t0.doctorId = t14.doctorId

-- ------------
-- 卡费 t15
-- ------------
left join 
(
    select round(sum(t.realFee),2)  as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where e.`itemCode` = 'jiuzhenka'    -- 病历本：工本费
    ) t 
    group by t.deptId,t.doctorId
) t15
on t0.deptId = t15.deptId and t0.doctorId = t15.doctorId


-- ------------
-- 现金 t16
-- ------------
left join 
(
    select round(sum(t.realFee),2)  as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where h.pay_method = 1      -- 现金
    ) t 
    group by t.deptId,t.doctorId
) t16
on t0.deptId = t16.deptId and t0.doctorId = t16.doctorId

-- ------------
-- 刷卡 t17
-- ------------
left join 
(
    select round(sum(t.realFee),2)  as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where h.pay_method = 2      -- 银行卡
    ) t 
    group by t.deptId,t.doctorId
) t17
on t0.deptId = t17.deptId and t0.doctorId = t17.doctorId

-- ------------
-- 预存款 t18
-- ------------
left join 
(
    select round(sum(t.realFee),2)  as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where e.`itemCode` = 'yujian' -- 预检：预存款
    ) t 
    group by t.deptId,t.doctorId
) t18
on t0.deptId = t18.deptId and t0.doctorId = t18.doctorId



-- ------------
-- 预存款 t19
-- ------------
left join 
(
    select round(sum(t.realFee),2)  as num,t.deptId, doctorId,t.deptName,t.doctorName from
    ( 
        select 
            a.data_source,
            a.is_appoint_resource,
            a.subsequent_visit,
            a.dept_id as deptId,
            a.appointment_doctor_id as doctorId,
            a.appointment_state,
            a.dept_name as deptName,
            a.appointment_doctor_name as doctorName, 
            CONCAT(a.`appointment_date`," ",a.appointment_starttime) as startTime,
            CONCAT(a.`appointment_date`," ",a.appointment_endtime) as endTime,
            f.preFee,                     -- 原价
            (f.preFee - f.discountFee - f.promotionBenefitFee - f.couponFee - f.itemBenefitFee     - f.memberCardBenefitFee - f.itemComInvBenefitFee) * f.discount AS realFee,
            f.discount,
            h.flow_fee,
            h.`charge`,
            h.`pay_method`
        from `thc_arrange`.`bpm_appointment` a
        inner join `thc_sob`.`bpm_service_order` b on a.orderId = b.id
        inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id =  b.id and c.id = a.`order_item_id`
        inner join `thc_rcm`.`Cs_AccountBill` d on d.orderID = b.id and d.`isDelete` = 0
        inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId
        inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id
        inner join `thc_rcm`.`Cs_Settlement` g on g.id = f.settlementID and g.settlementType=2 and g.`isDelete` = 0 -- 门诊挂号
        inner join `thc_rcm`.`pay_trade_log` h on h.`settlement_id` = g.id and h.type = 1
        where a.data_source = 2
    ) t 
    group by t.deptId,t.doctorId
) t19
on t0.deptId = t19.deptId and t0.doctorId = t19.doctorId

 

转载于:https://www.cnblogs.com/guchunchao/p/10145013.html