统计季度数据

在万达的每一天

使用教程使用时更改后面日期选择后面日期更改后进行粘贴

连接名103 库名basy_data开始

查询各个等级在我们数据库中的总分值

select sum(FZ+CRFZ) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '1' AND pdlb IN ('2','3','5','11');
select sum(FZ+CRFZ) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '2' AND pdlb IN ('2','3','5','11');
select sum(FZ+CRFZ) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '3' AND pdlb IN ('2','3','5','11');

根据用户是职工还是居民进行分值计算

select sum(FZ+CRFZ) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '01' AND pdlb IN ('2','3','5','11');
select sum(FZ+CRFZ) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '02' AND pdlb IN ('2','3','5','11');

根据医疗机构等级查询总例数

select count(1) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '1' AND pdlb IN ('2','3','5','11');
select count(1) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '2' AND pdlb IN ('2','3','5','11');
select count(1) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '3' AND pdlb IN ('2','3','5','11');

根据用户参保类型进行计算

select count(1) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '01' AND pdlb IN ('2','3','5','11');
select count(1) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '02' AND pdlb IN ('2','3','5','11');

根据医院级别查询总费用

select sum(ZFY) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '1' AND pdlb IN ('2','3','5','11');
select sum(ZFY) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '2' AND pdlb IN ('2','3','5','11');
select sum(ZFY) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '3' AND pdlb IN ('2','3','5','11');

根据用户参保类型查询总费用

select sum(ZFY) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '01' AND pdlb IN ('2','3','5','11');
select sum(ZFY) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '02' AND pdlb IN ('2','3','5','11');

根据医院级别查询自费金额

select sum(ZFJE) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '1' AND pdlb IN ('2','3','5','11');
select sum(ZFJE) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '2' AND pdlb IN ('2','3','5','11');
select sum(ZFJE) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '3' AND pdlb IN ('2','3','5','11');

根据用户参保类型查询自费金额

select sum(ZFJE) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '01' AND pdlb IN ('2','3','5','11');
select sum(ZFJE) FROM tb_cis_main_zk where SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '02' AND pdlb IN ('2','3','5','11');

根据医院级别查询部分自付金额

select sum(B.BFZFZFY) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLJGDJ = '1' AND A.pdlb IN ('2','3','5','11');
select sum(B.BFZFZFY) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLJGDJ = '2' AND A.pdlb IN ('2','3','5','11');
select sum(B.BFZFZFY) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLJGDJ = '3' AND A.pdlb IN ('2','3','5','11');

根据用户参保类型查询部分自付金额

select sum(B.BFZFZFY) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLFKFS = '01' AND A.pdlb IN ('2','3','5','11');
select sum(B.BFZFZFY) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLFKFS = '02' AND A.pdlb IN ('2','3','5','11');

根据医院级别查询基本医疗费用

select sum(B.SBZJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLJGDJ = '1' AND A.pdlb IN ('2','3','5','11');
select sum(B.SBZJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLJGDJ = '2' AND A.pdlb IN ('2','3','5','11');
select sum(B.SBZJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLJGDJ = '3' AND A.pdlb IN ('2','3','5','11');

根据用户参保类型查询基本医疗费用

select sum(B.SBZJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLFKFS = '01' AND A.pdlb IN ('2','3','5','11');
select sum(B.SBZJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND A.YLFKFS = '02' AND A.pdlb IN ('2','3','5','11');

根据医院级别查询病种数量

SELECT COUNT(1) FROM (select FZDM,count(1) from tb_cis_main_zk WHERE SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '1' AND pdlb IN ('2','3','5','11') GROUP BY FZDM) A;
SELECT COUNT(1) FROM (select FZDM,count(1) from tb_cis_main_zk WHERE SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '2' AND pdlb IN ('2','3','5','11') GROUP BY FZDM) A;
SELECT COUNT(1) FROM (select FZDM,count(1) from tb_cis_main_zk WHERE SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '3' AND pdlb IN ('2','3','5','11') GROUP BY FZDM) A;

去重之后操作

SELECT COUNT(1) FROM (select FZDM,count(1) from tb_cis_main_zk WHERE SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND pdlb IN ('2','3','5','11') GROUP BY FZDM) A;

根据用户参保类型查询病种数量

SELECT COUNT(1) FROM (select FZDM,count(1) from tb_cis_main_zk WHERE SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '01' AND pdlb IN ('2','3','5','11') GROUP BY FZDM) A;
SELECT COUNT(1) FROM (select FZDM,count(1) from tb_cis_main_zk WHERE SCSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '02' AND pdlb IN ('2','3','5','11') GROUP BY FZDM) A;

根据医院级别查看出院人数

select count(1) FROM tb_cis_main_zk where CYSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '1' AND pdlb IN ('2','3','5','11');
select count(1) FROM tb_cis_main_zk where CYSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '2' AND pdlb IN ('2','3','5','11');
select count(1) FROM tb_cis_main_zk where CYSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLJGDJ = '3' AND pdlb IN ('2','3','5','11');

根据用户参保类型查询出院人数

select count(1) FROM tb_cis_main_zk where CYSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '01' AND pdlb IN ('2','3','5','11');
select count(1) FROM tb_cis_main_zk where CYSJ BETWEEN '2020-10-01' AND '2020-12-31' AND YLFKFS = '02' AND pdlb IN ('2','3','5','11');

basy_data 数据库结束

切换数据库为db_basy

根据医院级别查看机构权重

select avg(ZXQZXS) from tb_gzyb_dic_jgqz where JBDM = '1'
select avg(ZXQZXS) from tb_gzyb_dic_jgqz where JBDM = '2'
select avg(ZXQZXS) from tb_gzyb_dic_jgqz where JBDM = '3'

总权重系数

select avg(ZXQZXS) from tb_gzyb_dic_jgqz

验证规则 总费用-自费>甲类+乙类

自付金额 = 总费用 - 甲类费用-乙类费用 - 自费金额

基本医疗费用 = 甲类费用+乙类费用

根据医院级别查询部分自付金额

select sum(b.zfy-(b.XMJLJE+b.XMYLJE)-b.XMZFJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb b on A.YWXLH = b.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLJGDJ = '1' AND A.pdlb IN ('2','3','5','11');
select sum(b.zfy-(b.XMJLJE+b.XMYLJE)-b.XMZFJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb b on A.YWXLH = b.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLJGDJ = '2' AND A.pdlb IN ('2','3','5','11');
select sum(b.zfy-(b.XMJLJE+b.XMYLJE)-b.XMZFJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb b on A.YWXLH = b.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLJGDJ = '3' AND A.pdlb IN ('2','3','5','11');

根据用户参保类型查询部分自费总费用

select sum(b.zfy-(b.XMJLJE+b.XMYLJE)-b.XMZFJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb b on A.YWXLH = b.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLFKFS = '01' AND A.pdlb IN ('2','3','5','11');
select sum(b.zfy-(b.XMJLJE+b.XMYLJE)-b.XMZFJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb b on A.YWXLH = b.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLFKFS = '02' AND A.pdlb IN ('2','3','5','11');

根据医院级别查询基本医疗费用

select sum(B.XMJLJE+B.XMYLJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLJGDJ = '1' AND A.pdlb IN ('2','3','5','11');
select sum(B.XMJLJE+B.XMYLJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLJGDJ = '2' AND A.pdlb IN ('2','3','5','11');
select sum(B.XMJLJE+B.XMYLJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLJGDJ = '3' AND A.pdlb IN ('2','3','5','11');

根据用户参保类型查询基本医疗费用

select sum(B.XMJLJE+B.XMYLJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLFKFS = '01' AND A.pdlb IN ('2','3','5','11');
select sum(B.XMJLJE+B.XMYLJE) FROM tb_cis_main_zk A LEFT JOIN tb_basy_kc28_hb B on A.YWXLH = B.YWXLH where A.JSSJ BETWEEN '2021/7/1' AND '2021/9/30' AND A.YLFKFS = '02' AND A.pdlb IN ('2','3','5','11');