按科室统计 2.181222版本 关联查询join 不对
SQL：
select t0.deptName, t0.deptId, t0.startTime, t0.endTime, IFNULL(t0.num,0) as num0, IFNULL(t1.num,0) as num1, IFNULL(t2.num,0) as num2, 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(t14.num,0) as num14, IFNULL(t15.num,0) as num15, IFNULL(t16.num,0) as num16, IFNULL(t17.num,0) as num17, IFNULL(t18.num,0) as num18, IFNULL(t19.num,0) as num19, IFNULL(t20.num,0) as num20, IFNULL(t21.num,0) as num21, IFNULL(t22.num,0) as num22 from -- ------------ -- 总挂号数 -- ------------ ( select t.startTime, t.endTime, count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.appointment_state, a.dept_id as deptId, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` and c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and f.isDelete = 0 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 1=1 and a.del_flag = 0 ) t group by t.deptId ) t0 -- ------------ -- 退号 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` and c.del_flag = 0 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 and e.isDelete =0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and f.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.appointment_state = -1 ) t group by t.deptId ) t1 on t0.deptId = t1.deptId -- ------------ -- 实际挂号数 = 初诊数 + 复诊数 + 转诊数 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit in (0,1,2) ) t group by t.deptId ) t2 on t0.deptId = t2.deptId -- ------------ -- 初诊 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 0 ) t group by t.deptId ) t4 on t0.deptId = t4.deptId -- ------------ -- 复诊 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 1 ) t group by t.deptId ) t5 on t0.deptId = t5.deptId -- ------------ -- 体检 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 4 ) t group by t.deptId ) t6 on t0.deptId = t6.deptId -- ------------ -- 团队 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 7 ) t group by t.deptId ) t7 on t0.deptId = t7.deptId -- ------------ -- 简易 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 5 ) t group by t.deptId ) t8 on t0.deptId = t8.deptId -- ------------ -- 转诊 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 2 ) t group by t.deptId ) t9 on t0.deptId = t9.deptId -- ------------ -- 疫苗 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.subsequent_visit = 2 ) t group by t.deptId ) t10 on t0.deptId = t10.deptId -- ------------ -- t11 微信数 -- ------------ left join ( select count(1) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND a.data_source = 2 ) t group by t.deptId ) t11 on t0.deptId = t11.deptId -- ------------ -- 挂号费 t13 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 ) t group by t.deptId ) t13 on t0.deptId = t13.deptId -- ------------ -- 急诊费 t14 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND e.itemCode = 'jiajifei' -- 加急费：急诊费 ) t group by t.deptId ) t14 on t0.deptId = t14.deptId -- ------------ -- 工本费 t15 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND e.itemCode = 'bingliben' -- 病历本：工本费 ) t group by t.deptId ) t15 on t0.deptId = t15.deptId -- ------------ -- 卡费 t16 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 AND e.itemCode = 'jiuzhenka' -- 就诊卡：卡费 ) t group by t.deptId ) t16 on t0.deptId = t16.deptId -- ------------ -- 现金 t17 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` and c.del_flag=0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and f.isDelete=0 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 1=1 and a.del_flag = 0 -- 门诊 and h.pay_method = 1 -- 银行卡 ) t group by t.deptId ) t17 on t0.deptId = t17.deptId -- ------------ -- 银行卡 t18 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 and h.pay_method = 2 -- 银行卡 ) t group by t.deptId ) t18 on t0.deptId = t18.deptId -- ------------ -- 预检 t19 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 and e.itemCode = 'yujian' -- 预检：预存款 ) t group by t.deptId ) t19 on t0.deptId = t19.deptId -- ------------ -- 微信金额t20 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 and a.data_source = 2 ) t group by t.deptId ) t20 on t0.deptId = t20.deptId -- ------------ -- 应收金额 t21 -- ------------ left join ( select round(sum(t.preFee),2) as num,t.deptId, t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 ) t group by t.deptId ) t21 on t0.deptId = t21.deptId -- ------------ -- 实收金额 t22 -- ------------ left join ( select round(sum(t.realFee),2) as num,t.deptId,t.deptName from ( select a.data_source, a.is_appoint_resource, a.subsequent_visit, a.dept_id as deptId, a.appointment_state, a.dept_name as deptName, 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 and b.del_flag = 0 inner join `thc_sob`.`bpm_service_order_item` c on c.service_order_id = b.id and c.id = a.`order_item_id` AND c.del_flag = 0 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 and e.isDelete = 0 inner join `thc_rcm`.`Cs_SettlementDetail` f on f.accountBillID = d.id and f.accountBillDetailID = e.id and e.isDelete = 0 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 1=1 and a.del_flag = 0 ) t group by t.deptId ) t22 on t0.deptId = t22.deptId order by t0.startTime asc
