MySQL-实习期间常用sql整理

1、物价比对（分为医保、his）

*调优 先筛选再关联

select a.itemcode,a.itemname,a.itemname_src,a.itemprice,a.chargeunit,a.orgname,
b.wf_itemname,b.price as wf_price,b.unit as wf_unit,
sum(a.fee),sum(a.amount)
from (select * from medical_gaomi.dwb_charge_detail where etl_source_name = '医保') as a
left join tmp.wf_item_price as b
on a.itemcode = b.yx_itemcode
where a.itemprice > b.price
group by a.itemcode,a.itemname,a.itemname_src,a.itemprice,a.etl_source_name,b.price,a.orgname,a.chargeunit,b.unit,b.wf_itemname
order by sum(a.fee) desc

2、医保结算与his结算关联（分为住院、门诊）

*创建临时表+查找

门诊

with t_yb_settlement as(
select a.settlementid,a.visitid,a.clientname,a.orgid,a.orgname,
a.deptid,a.admitdate,b.visitdate,b.leavedate,a.settlementdate
from ods_yb_settlement as a left join ods_yb_master_info as b
on a.visitid = b.visitid ),
t_his_mz_settlement as(
select a.yb_settlementid,a.settlementid,a.his_mz_id,a.clientname,a.orgid,a.orgname,
b.deptid,b.visitdate,a.settlementdate from ods_his_mz_settlement as a
left join ods_his_mz_master_info as b on a.his_mz_id = b.his_mz_id)

SELECT concat(yb.visitid,'_',his.his_mz_id) as new_visitid,
yb.settlementid as '医保settlementid',yb.visitid as '医保visitid',
yb.clientname as '医保clientname',yb.orgname as '医保orgname',
yb.deptid as '医保deptid',yb.admitdate as '医保admitdate',yb.visitdate as '医保visitdate',
yb.leavedate as '医保leavedate',yb.settlementdate as '医保settlementdate',
his.yb_settlementid as 'HIS yb_settlementid',his.settlementid as 'his settlementid',
his.his_mz_id as 'HIS his_mz_id',his.clientname as 'HIS clientname',
his.orgname as 'HIS orgname',his.deptid as 'HIS deptid',
his.visitdate as 'HIS visitdate',his.settlementdate as 'HIS settlementdate'
from t_yb_settlement as yb LEFT JOIN t_his_mz_settlement as his
on yb.settlementid = his.yb_settlementid
where left(yb.visitdate,10) != left(his.visitdate,10)
limit 1000

住院

with t_yb_settlement as(
select a.settlementid,a.visitid,a.clientname,a.orgid,a.orgname,
a.deptid,a.admitdate,b.visitdate,b.leavedate,a.settlementdate
from ods_yb_settlement as a left join ods_yb_master_info as b
on a.visitid = b.visitid ),
t_his_zy_settlement as(
select a.yb_settlementid,a.settlementid,a.his_zy_id,a.clientname,a.orgid,a.orgname,
b.admit_deptid,b.admitdate,b.leavedate,a.settlementdate
from ods_his_zy_settlement as a left join ods_his_zy_master_info as b
on a.his_zy_id = b.his_zy_id)

SELECT concat(yb.visitid,'_',his.his_zy_id) as new_visitid,
yb.settlementid as '医保settlementid',yb.visitid as '医保visitid',
yb.clientname as '医保clientname',yb.orgname as '医保orgname',
yb.deptid as '医保deptid',yb.admitdate as '医保admitdate',yb.visitdate as '医保visitdate',
yb.leavedate as '医保leavedate',yb.settlementdate as '医保settlementdate',
his.yb_settlementid as 'HIS yb_settlementid',his.settlementid as 'his settlementid',
his.his_zy_id as 'HIS his_zy_id',his.clientname as 'HIS clientname',
his.orgname as 'HIS orgname',his.admit_deptid as 'HIS admit_deptid',
his.admitdate as 'HIS admitdate',his.leavedate as 'HIS leavedate',
his.settlementdate as 'HIS settlementdate'
from t_yb_settlement as yb LEFT JOIN t_his_zy_settlement as his
on yb.settlementid = his.yb_settlementid
where left(yb.leavedate,10) != left(his.leavedate,10)
limit 1000

3、关联缺失

select a.clientid from ods_yb_client a
left join ods_his_client b
on a.clientid=b.clientid
where b.clientid is null;

4、查询dwb层药品总价

SELECT itemcode,itemname,sum(amount) as '数量',itemprice, sum(fee) as '总金额'
from dwb_charge_detail
WHERE etl_source = 'A03'
GROUP BY itemcode,itemname,itemprice
ORDER BY sum(fee) DESC

5、重复记录查询 多字段

SELECT count(*) from src_his_test t1
where (select count(*) from src_his_test t2 
where t1.visitid = t2.visitid
and t1.get_date = t2.get_date
and t1.test_group = t2.test_group
and t1.id = t2.id
and t1.test_groupno = t2.test_groupno)>1

6、case when... then

WITH ta as (SELECT * from (
SELECT orgname,settlementid,visittype from ods_yb_settlement ) a 
LEFT join
(SELECT orgname as c1,yb_settlementid as c2 from ods_his_mz_settlement ) b
on a.settlementid=b.c2 and a.orgname=b.c1)
SELECT visittype,count(*),sum (CASE when( c2 is not null) then 1 else 0 end) 
from ta a, ods_mapping_org b where 
 a.orgname=b.yx_orgname and town_his_sign ='乡镇his'
group by visittype