-- 查询门诊挂号退费的账单：有4条记录

select * from `thc_rcm`.`Cs_AccountBill` a

where a.orderSource = 1 and a.orderType = 3 and a.returnFlag = 1

 

查询这4个退费订单：

 

select * from `thc_sob`.`bpm_service_order` b

where b.id in (

'ce33db6a-05f6-11e9-9ee0-0242ac110026',

'8077a88f-05fd-11e9-9ee0-0242ac110026',

'df40c79c-04fe-11e9-9ee0-0242ac110026',

'cf800ee7-05f7-11e9-9ee0-0242ac110026');

 

status 3个是-1，一个是0

 

 

-- 查询挂号单退费记录：只查到了2个

select *

from `thc_arrange`.`bpm_appointment` a

where appointment_date in ('2018-12-21','2018-12-22') and a.appointment_state = -1

 

查询条件：非退款

-----------------------------------------------

select * 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 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 0

inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is NULL

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 and g.returnFlag = 0 -- 门诊挂号

 

查询条件：退款

-----------------------------------------------

select * 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 and d.orderSource = 1 and d.orderType = 3 and d.returnFlag = 1

inner join `thc_rcm`.`Cs_AccountBillDetail` e on d.id = e.AccountBillId and e.itemClass = 1 and e.returnFlag is not NULL

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 and g.returnFlag = 1 -- 门诊挂号

 

 

 

 

 

 

 

按照医生统计的前2个是替换好的联查join SQL

---------------------------------------------------