select * from ( select t.itemName, t.patient_name, t.price, t.fatherItemClassName, t.fatherItemClass, t.createBy, t.createTime, sum(t.quantity) as quantity, sum(t.totalMoney) AS totalMoney from( select b.itemID, b.itemName, d.`name` AS patient_name, e.name AS fatherItemClassName, g.clinic_receipt_type AS fatherItemClass, k.docname AS createBy, DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime, round(b.quantity) AS quantity, round(b.price,2) AS price, b.mealFlag, TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM `thc_rcm`.`Cs_Settlement` a JOIN `thc_rcm`.`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 JOIN `thc_sob`.`bpm_health_service` g ON g.id = b.itemID JOIN `thc_thc_platform_core`.`tm_value_set_item` e ON e.code = g.clinic_receipt_type JOIN `thc_thc_platform_core`.`tm_value_set` f ON e.set_code = f.set_code WHERE a.isDelete= 0 AND a.returnFlag = 0 AND f.set_code = 'THC_CPOE_OutpatientReceipt' AND a.id="${id}" AND b.mealFlag = 0 ) t group by t.itemName, t.patient_name, t.fatherItemClass, t.fatherItemClassName union select t.itemName, t.patient_name, t.price, t.fatherItemClassName, t.fatherItemClass, t.createBy, t.createTime, sum(t.quantity) as quantity, 0 AS totalMoney from( select b.itemID, b.itemName, d.name AS patient_name, e.name AS fatherItemClassName, g.clinic_receipt_type AS fatherItemClass, k.docname AS createBy, DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime, round(b.quantity) AS quantity, round(b.price,2) AS price, TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM `thc_rcm`.`Cs_Settlement` a JOIN `thc_rcm`.`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 JOIN `thc_sob`.`bpm_health_service` g on g.id = b.itemID JOIN `thc_thc_platform_core`.`tm_value_set_item` e on e.code = g.clinic_receipt_type JOIN `thc_thc_platform_core`.`tm_value_set` f on e.set_code = f.set_code WHERE a.isDelete= 0 AND f.set_code = 'THC_CPOE_OutpatientReceipt' AND a.id="${id}" AND b.mealFlag = 1 ) t group by t.itemName, t.patient_name, t.fatherItemClass, t.fatherItemClassName ) tt limit 10 offset ${startIndex}
select sum(totalMoney) as totalMoney, sum(quantity) as quantity, fatherItemClass, fatherItemClassName, CASE fatherItemClass -- 初复诊 WHEN 21 THEN 1 -- 西药费 WHEN 22 THEN 2 -- 中成药费 WHEN 14 THEN 3 -- 材料费 WHEN 12 THEN 4 -- 其它费 END AS itemClassOrder from ( select * from ( select t.itemName, t.patient_name, t.price, t.fatherItemClassName, t.fatherItemClass, t.createBy, t.createTime, sum(t.quantity) as quantity, sum(t.totalMoney) AS totalMoney from( select b.itemID, b.itemName, d.`name` AS patient_name, e.name AS fatherItemClassName, g.clinic_receipt_type AS fatherItemClass, k.docname AS createBy, DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime, round(b.quantity) AS quantity, round(b.price,2) AS price, b.mealFlag, TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM `thc_rcm`.`Cs_Settlement` a JOIN `thc_rcm`.`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 JOIN `thc_sob`.`bpm_health_service` g ON g.id = b.itemID JOIN `thc_thc_platform_core`.`tm_value_set_item` e ON e.code = g.clinic_receipt_type JOIN `thc_thc_platform_core`.`tm_value_set` f ON e.set_code = f.set_code WHERE a.isDelete= 0 AND a.returnFlag = 0 AND f.set_code = 'THC_CPOE_OutpatientReceipt' AND a.id="${id}" AND b.mealFlag = 0 ) t group by t.itemName, t.patient_name, t.fatherItemClass, t.fatherItemClassName union select t.itemName, t.patient_name, t.price, t.fatherItemClassName, t.fatherItemClass, t.createBy, t.createTime, sum(t.quantity) as quantity, 0 AS totalMoney from( select b.itemID, b.itemName, d.name AS patient_name, e.name AS fatherItemClassName, g.clinic_receipt_type AS fatherItemClass, k.docname AS createBy, DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime, round(b.quantity) AS quantity, round(b.price,2) AS price, TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM `thc_rcm`.`Cs_Settlement` a JOIN `thc_rcm`.`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 JOIN `thc_sob`.`bpm_health_service` g on g.id = b.itemID JOIN `thc_thc_platform_core`.`tm_value_set_item` e on e.code = g.clinic_receipt_type JOIN `thc_thc_platform_core`.`tm_value_set` f on e.set_code = f.set_code WHERE a.isDelete= 0 AND f.set_code = 'THC_CPOE_OutpatientReceipt' AND a.id="${id}" AND b.mealFlag = 1 ) t group by t.itemName, t.patient_name, t.fatherItemClass, t.fatherItemClassName ) kk limit 10 offset 0 ) tt where 1=1 and tt.fatherItemClass in (21,22,14,12) group by fatherItemClass,fatherItemClassName order by itemClassOrder asc
新版本，去掉了套餐
===================================================
select t.itemName, t.patient_name as patientName, t.price, t.fatherItemClassName, t.fatherItemClass, t.createBy, t.createTime, sum(t.quantity) as quantity, sum(t.totalMoney) AS totalMoney from( select b.itemID, b.itemName, d.`name` AS patient_name, e.name AS fatherItemClassName, g.clinic_receipt_type AS fatherItemClass, k.docname AS createBy, DATE_FORMAT(a.createtime,'%Y-%m-%d') AS createtime, round(b.quantity) AS quantity, round(b.price,2) AS price, b.mealFlag, TRUNCATE(b.preFee - b.discountFee - b.promotionBenefitFee - b.couponFee - b.itemBenefitFee - b.memberCardBenefitFee - b.itemComInvBenefitFee,2) AS totalMoney, c.AccountBillId, b.settlementID FROM `thc_rcm`.`Cs_Settlement` a JOIN `thc_rcm`.`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 JOIN `thc_sob`.`bpm_health_service` g ON g.id = b.itemID JOIN `thc_thc_platform_core`.`tm_value_set_item` e ON e.code = g.clinic_receipt_type JOIN `thc_thc_platform_core`.`tm_value_set` f ON e.set_code = f.set_code WHERE a.isDelete= 0 AND a.returnFlag = 0 AND f.set_code = 'THC_CPOE_OutpatientReceipt' AND a.id="${id}" AND b.mealFlag = 0 ) t group by t.itemID, t.fatherItemClass order by t.itemID limit 10 offset ${startIndex}