博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
新SQL temp
阅读量:6959 次
发布时间:2019-06-27

本文共 8670 字,大约阅读时间需要 28 分钟。

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.AccountBillIdINNER JOIN `thc_passport`.`patient` p on a.patientID = p.idINNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.clinicID = dept.idLEFT  JOIN `thc_passport`.`patient_org` e on a.patientID = e.patient_idLEFT  JOIN `thc_passport`.`contact` i on a.patientID = i.patient_idwhere `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.AccountBillIdINNER JOIN `thc_passport`.`patient` p on a.patientID = p.idINNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.clinicID = dept.idINNER  JOIN `thc_passport`.`patient_org` e on a.patientID = e.patient_idwhere a.is_refund = 0 and `quantity` > 0  and b.returnQuantity < b.`quantity` -- and b.`returnQuantity` > 0;limit 10;select b.from  `thc_warehouse`.`clinic` ajoin `thc_warehouse`.`clinic_property` b on a.id = b.clinic_idwhere a.id in (1,2002)order by b.clinic_id,b.property_code-- join `thc_warehouse`.`clinic_property_value` cselect  b.code,b.name,b.value,b.idfrom `thc_warehouse`.`sys_type` ajoin `thc_warehouse`.`sys_type_info` b on a.id = b.sys_type_id where a.code = 'THC_RCM_COUNTRY' order by valueselect * 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.AccountBillIdINNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.clinicID = dept.idwhere    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.AccountBillIdINNER JOIN `thc_rcm`.`Cs_SettlementDetail` d on d.accountBillDetailID = b.id and d.accountBillID = a.idINNER JOIN `thc_rcm`.`Cs_Settlement` c on c.id = d.settlementIDINNER JOIN `thc_passport`.`patient` p on a.patientID = p.idINNER JOIN `thc_sob`.`bpm_serv_provider` dept on a.orderDept = dept.idINNER JOIN `thc_passport`.`patient_org` e on a.patientID = e.patient_idwhere     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

 

转载于:https://www.cnblogs.com/guchunchao/p/10382861.html

你可能感兴趣的文章
多线程之死锁就是这么简单
查看>>
Python字符串格式化
查看>>
朴素贝叶斯算法文本分类原理
查看>>
轰轰烈烈的搭建Spring + Spring MVC + Mybatis
查看>>
MySQL 单机多实例
查看>>
微信小程序入门到实战(二)
查看>>
graphql-java使用手册:part4 订阅(Subscriptions)
查看>>
理解js对象
查看>>
2017-10-07 前端日报
查看>>
Go 领军人物谢孟军:智能制造渴望银弹,首先要摒弃偏见
查看>>
函数式编程与面向对象编程[3]:Scala的OOP-FP混合式编程与抽象代数理论
查看>>
statsd on steroid
查看>>
【mongoDB运维篇③】replication set复制集
查看>>
php中查询mysql如何在IN 中用数组
查看>>
2015 年十佳 HTML5 应用
查看>>
php对象设计进阶
查看>>
python程序的调试
查看>>
启动级别:init 0-6
查看>>
mybatis深入理解(一)之 # 与 $ 区别以及 sql 预编译
查看>>
Java四种引用类型
查看>>