hcjk_fr 查询SQL，支持组套

t.itemName, t.patient_name, t.price, t.fatherItemClass, t.fatherItemClassName, t.createBy, t.createTime, sum(t.quantity) as quantity, sum(t.totalMoney) AS totalMoney -- select * from( -- 退费 select b.itemID, b.itemName, d.`name` AS patient_name, c.fatherItemClass, c.fatherItemClassName, k.docname as createBy, DATE_FORMAT(a.createtime,‘%Y-%m-%d‘) as createtime, round(b.quantity) as quantity, round(b.price,2) as price, round(b.fee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM Cs_Settlement a JOIN Cs_SettlementDetail b on a.id = b.settlementID JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id JOIN ( SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname FROM thc_warehouse.staff_record u LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = ‘SXX000083‘ AND u.id = u1.staff_record_id ) k ON a.createBy = k.docid JOIN `thc_c_union`.`patient` d ON a.patientID = d.id where a.isDelete = 0 and a.returnFlag = 1 and b.itemID != ‘VS00000000‘ AND a.returnId="245e8eb18b584755bee1260b3753ec00" and c.packageId is null union -- 正向支付 select b.itemID, b.itemName, d.`name` AS patient_name, c.fatherItemClass, c.fatherItemClassName, k.docname as createBy, DATE_FORMAT(a.createtime,‘%Y-%m-%d‘) as createtime, round(b.quantity) as quantity, round(b.price,2) as price, round(b.fee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM Cs_Settlement a JOIN Cs_SettlementDetail b on a.id = b.settlementID JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id JOIN ( SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname FROM thc_warehouse.staff_record u LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = ‘SXX000083‘ AND u.id = u1.staff_record_id ) k ON a.createBy = k.docid JOIN `thc_c_union`.`patient` d ON a.patientID = d.id where a.isDelete= 0 and a.returnFlag = 0 AND a.id="245e8eb18b584755bee1260b3753ec00" and c.packageId is null ) t group by t.itemName, t.patient_name, -- t.price, t.fatherItemClass, t.fatherItemClassName -- t.createBy, -- t.createTime having (totalMoney > 0 and quantity > 0) union select q.itemName, q.patient_name, sum(q.price) as price, q.fatherItemClass, q.fatherItemClassName, q.createBy, q.createTime, sum(q.quantity) as quantity, sum(q.totalMoney) AS totalMoney from( select t.itemName, t.patient_name, sum(t.price) as price, t.fatherItemClass, t.fatherItemClassName, t.createBy, t.createTime, 1 as quantity, sum(t.totalMoney) AS totalMoney -- select * from( -- 退费 select b.packageId as itemID, b.packageName as itemName, d.`name` AS patient_name, c.fatherItemClass, c.fatherItemClassName, k.docname as createBy, DATE_FORMAT(a.createtime,‘%Y-%m-%d‘) as createtime, round(b.quantity) as quantity, round(b.price,2) as price, round(b.fee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM Cs_Settlement a JOIN Cs_SettlementDetail b on a.id = b.settlementID JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id JOIN ( SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname FROM thc_warehouse.staff_record u LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = ‘SXX000083‘ AND u.id = u1.staff_record_id ) k ON a.createBy = k.docid JOIN `thc_c_union`.`patient` d ON a.patientID = d.id where a.isDelete = 0 and a.returnFlag = 1 and b.itemID != ‘VS00000000‘ AND a.returnId="245e8eb18b584755bee1260b3753ec00" and c.packageId is not null union -- 正向支付 select b.packageId as itemID, b.packageName as itemName, d.`name` AS patient_name, c.fatherItemClass, c.fatherItemClassName, k.docname as createBy, DATE_FORMAT(a.createtime,‘%Y-%m-%d‘) as createtime, round(b.quantity) as quantity, round(b.price,2) as price, round(b.fee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM Cs_Settlement a JOIN Cs_SettlementDetail b on a.id = b.settlementID JOIN `thc_rcm`.`Cs_AccountBillDetail` c ON b.accountBillDetailID = c.id JOIN ( SELECT u.clinic_id AS clinicid, u.id AS docid,u.name AS docname FROM thc_warehouse.staff_record u LEFT JOIN thc_warehouse.staff_record_property u1 ON u1.property_code = ‘SXX000083‘ AND u.id = u1.staff_record_id ) k ON a.createBy = k.docid JOIN `thc_c_union`.`patient` d ON a.patientID = d.id where a.isDelete= 0 and a.returnFlag = 0 AND a.id="245e8eb18b584755bee1260b3753ec00" and c.packageId is not null ) t group by t.itemName, t.patient_name, -- t.price, t.fatherItemClass, t.fatherItemClassName, t.AccountBillId -- t.createBy, -- t.createTime having (totalMoney > 0 and quantity > 0) ) q -- order by t.itemID limit 10 offset 0 -- ${startIndex}

 

 

 

 

hcjk_fr 查询SQL，支持组套

标签：mat   tar   nbsp   date   查询   form   _for   account   package