select
a.createtime, -- 日期
dept.name as deptName, -- 科室
(select t.docname from (
SELECT u.clinic_id AS clinicid, u.id AS docid,u1.property_value 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
) t where t.docid = a.orderDoctor ) AS doctor -- 医生
e.outpatient_number, -- 门诊编号 档案号
p.name, -- 客户名字
p.age, -- 年龄
p.country, -- 国籍/语言
p.language, -- 语言
b.itemClassName, -- 项目类型
b.itemName, -- 项目名称
-- b.fatherItemClassName,
-- b.itemDisName, -- b.returnQuantity, -- 退货数量
b.price, -- 原单价
ROUND(b.discount * 100,0), -- 折扣比
round(b.price * b.quantity,4) as subPrice, -- 折后价
b.quantity, -- 购买数量
b.amount, -- 折后总金额
b.preFee, -- 原总价 from
`thc_rcm`.`Cs_AccountBill` a
INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` b on a.id =b.AccountBillId
INNER JOIN `thc_passport`.`patient` p on a.patientID = p.id
INNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.clinicID = dept.id
LEFT JOIN `thc_passport`.`patient_org` e on a.patientID = e.patient_id
LEFT JOIN `thc_passport`.`contact` i on a.patientID = i.patient_id where `quantity` > 0 and a.is_refund = 0 and b.returnQuantity < b.`quantity` -- and b.`returnQuantity` > 0; select * from thc_sob.bpm_serv_provider
select
a.createtime, -- 日期
( select name from `thc_passport`.`city` where id =
(
select JSON_UNQUOTE(JSON_EXTRACT(property_value,'$.city'))
from `thc_warehouse`.`clinic_property`
where property_code = 'SXX000059'
and property_value is not null
and JSON_UNQUOTE(JSON_EXTRACT(property_value,'$.city')) is not null
and clinic_id = a.clinicID
)
) as zenSuoCity, -- 城市
(select property_value from `thc_warehouse`.`clinic_property` where property_code = 'SXX000055' and clinic_id = a.clinicID) as zenSuo, -- 诊所
(select property_value from `thc_warehouse`.`clinic_property` where property_code = 'SXX000063' and clinic_id = a.clinicID) as zenSuoCode, -- 诊所代码
dept.name as deptName, -- 科室
(select t.docname from (
SELECT u.clinic_id AS clinicid, u.id AS docid,u1.property_value 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
) t where t.docid = a.orderDoctor ) AS doctor, -- 医生
e.patient_number, -- 档案号
p.name, -- 客户名字
p.age, -- 年龄
p.country as countryBak,
p.language as languageBak,
(
select bb.name
from `thc_warehouse`.`sys_type` aa
join `thc_warehouse`.`sys_type_info` bb on aa.id = bb.sys_type_id
where aa.code = 'THC_RCM_COUNTRY' and bb.id = p.country
) as country, -- 国籍
(
select bb.name
from `thc_warehouse`.`sys_type` aa
join `thc_warehouse`.`sys_type_info` bb on aa.id = bb.sys_type_id
where aa.code = 'PASSPORT_LANGUAGE' and bb.id = p.language
) as language, -- 语言 b.itemClassName, -- 项目类型
b.itemName, -- 项目名称
b.price, -- 原价
ROUND(b.discount * 100,0), -- 折扣比
round(b.price * b.quantity,4) as subPrice, -- 折后价
ROUND(b.quantity,0), -- 数量
b.amount, -- 折后总金额
b.preFee -- 原总价
from
`thc_rcm`.`Cs_AccountBill` a
INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` b on a.id =b.AccountBillId
INNER JOIN `thc_passport`.`patient` p on a.patientID = p.id
INNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.clinicID = dept.id
INNER JOIN `thc_passport`.`patient_org` e on a.patientID = e.patient_id where a.is_refund = 0 and `quantity` > 0 and b.returnQuantity < b.`quantity` -- and b.`returnQuantity` > 0; limit 10; select b.
from `thc_warehouse`.`clinic` a
join `thc_warehouse`.`clinic_property` b on a.id = b.clinic_id
where a.id in (1,2002)
order by b.clinic_id,b.property_code -- join `thc_warehouse`.`clinic_property_value` c select b.code,b.name,b.value,b.id
from `thc_warehouse`.`sys_type` a
join `thc_warehouse`.`sys_type_info` b on a.id = b.sys_type_id
where a.code = 'THC_RCM_COUNTRY'
order by value select * from `thc_warehouse`.`sys_type_info` where code like 'htc_00000009%'; select id,name from `thc_passport`.`city` where id like '___0000000' select * from `thc_passport`.`city` where id not like '___0000000' and id = REGEXP '1010[1-9]{1}[0-9]?'
SQL2:消费项目汇总统计
select
( select name from `thc_passport`.`city` where id =
(
select JSON_UNQUOTE(JSON_EXTRACT(property_value,'$.city'))
from `thc_warehouse`.`clinic_property`
where property_code = 'SXX000059'
and property_value is not null
and JSON_UNQUOTE(JSON_EXTRACT(property_value,'$.city')) is not null
and clinic_id = a.clinicID
)
) as zenSuoCity, -- 城市
(select property_value from `thc_warehouse`.`clinic_property` where property_code = 'SXX000055' and clinic_id = a.clinicID) as zenSuo, -- 诊所
(select property_value from `thc_warehouse`.`clinic_property` where property_code = 'SXX000063' and clinic_id = a.clinicID) as zenSuoCode, -- 诊所代码
dept.name as deptName, -- 科室
(select t.docname from (
SELECT u.clinic_id AS clinicid, u.id AS docid,u1.property_value 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
) t where t.docid = a.orderDoctor ) AS doctor, -- 医生
b.itemClassName, -- 项目类型
b.itemName, -- 项目名称
ROUND(sum(b.quantity),0) AS quantity, -- 总数量
sum(b.preFee) as sumFee, -- 原价总价
sum(b.amount) as subSumFee -- 折后总金额
from
`thc_rcm`.`Cs_AccountBill` a
INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` b on a.id =b.AccountBillId
INNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.clinicID = dept.id
where
a.is_refund = 0 -- 是买非退
and b.quantity > 0 -- 数量大于0
and b.returnQuantity < b.quantity -- 未退完的
group by b.itemClassName,b.itemName
-- limit 10
SQL3 monthly sale report（统计人次）
select
b.* from
`thc_rcm`.`Cs_AccountBill` a
INNER JOIN `thc_rcm`.`Cs_AccountBillDetail` b on a.id =b.AccountBillId
INNER JOIN `thc_rcm`.`Cs_SettlementDetail` d on d.accountBillDetailID = b.id and d.accountBillID = a.id
INNER JOIN `thc_rcm`.`Cs_Settlement` c on c.id = d.settlementID
INNER JOIN `thc_passport`.`patient` p on a.patientID = p.id
INNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.orderDept = dept.id
INNER JOIN `thc_passport`.`patient_org` e on a.patientID = e.patient_id where
a.is_refund = 0 -- 是买非退
and b.quantity > 0 -- 数量大于0
and b.returnQuantity < b.quantity -- 未退完的
limit 10 ${if(len(startTime) == 0,"","AND a.createtime >= '" + startTime + "'")} -- 开始时间
${if(len(endTime) == 0,"","AND a.createtime <= '" + endTime + "'")} -- 结束时间
${if(len(zenSuo) == 0,"","AND a.clinicID = '" + zenSuo + "'")} -- 诊所
${if(len(zenSuoCode) == 0,"","AND a.clinicID = '" + zenSuoCode + "'")} -- 诊所代码
${if(len(deptId) == 0,"","AND a.orderDept = '" + deptId + "'")} -- 科室
${if(len(doctor) == 0,"","AND a.orderDoctor = '" + doctor + "'")} -- 医生
${if(len(patientNum) == 0,"","AND e.patient_number like '%" + patientNum + "%'")} -- 客户档案号
${if(len(itemClassName) == 0,"","AND b.itemClassName like '%" + itemClassName + "%'")} -- 项目类型
${if(len(itemName) == 0,""," AND (b.itemName like '%" + itemName + "%' or b.itemCode like '%"+ itemName + "%')")} -- 项目
) t
where 1=1
${if(len(city) == 0,"","AND t.zenSuoCity = '" + city + "'")} -- 城市
order by t.createtime desc