--药品分类表
create table C_SUBJECT
(
ID NUMBER(19) not null,--分类id
SUBJECT_NAME VARCHAR2(50) not null,
ZJM VARCHAR2(10) not null,
SUBJECT_JB NUMBER(19),
SUBJECT_PARENT NUMBER(19),
IF_DEL VARCHAR2(1) default '0' not null,
FP_SUBJECT VARCHAR2(50),
BB_SUBJECT VARCHAR2(50),
FYQD_SUBJECT VARCHAR2(50),
ORDER_NO NUMBER(3)
);
--药品表
create table BASIC_FEEITEM
(
ID NUMBER(19) not null,--药品id
CODE VARCHAR2(9) not null,
NAME VARCHAR2(200) not null,--药品名字
SNAME VARCHAR2(200),
ENAME VARCHAR2(200),
ISPAY NUMBER(19) not null,
SERVICE_TYPE_ID NUMBER(19) not null,
FINANCE_PAYTYPE_ID NUMBER(19),
PROJ_CONTENT VARCHAR2(2000),
EXCLV_CONTENT VARCHAR2(2000),
DESCRIPTION VARCHAR2(2000),
PRICE_UNIT NUMBER(19),
ISPUBLIC NUMBER(19),
GROUP_CATEGORY VARCHAR2(50),
INVOICE_TYPE_ID NUMBER(19),
STATISTIC_TYPE_ID NUMBER(19),
NUMERIC_CODE VARCHAR2(20),
PINYIN_CODE VARCHAR2(20),
WUBI_CODE VARCHAR2(20), SUBJECT_NO NUMBER(19)--药品分类
);
--处方明细表
create table PRE_CHARGE_RECIPE_D
(
ID NUMBER(19) not null,
RECIPE_NO NUMBER(19) not null,--处方编号
CF_IN_NO NUMBER(2) not null,
ZH NUMBER(2) not null,
BM NUMBER(19) not null,--药品编码
MC VARCHAR2(50) not null,
DJ NUMBER(7,2) not null,
ZJE NUMBER(9,2) not null,--总费用
IF_PS VARCHAR2(1) default '0',
IF_ZXZX VARCHAR2(1) default '0',
IF_ZB VARCHAR2(1) default '0',
TSYF VARCHAR2(100),
YBBXFY NUMBER(9,2),--医保费用
QTBXFY NUMBER(9,2)--其它报销费用
);输入查询条件为处方明细表中处方编号 RECIPE_NO一个处方号可能对应几个药品,根据药品的分类BASIC_FEEITEM.SUBJECT_NO(C_SUBJECT.ID),统计分类费用
如:
-----------------------------------
SUBJECT_NAME ZJE zifeijine
中药 20 12
西药 15 8-------------------------------
SUBJECT_NAME(C_SUBJECT.SUBJECT_NAME),ZJE(sum(PRE_CHARGE_RECIPE_D.zje)) zifeijine(sum(ZJE-YBBXFY-QTBXFY))
create table C_SUBJECT
(
ID NUMBER(19) not null,--分类id
SUBJECT_NAME VARCHAR2(50) not null,
ZJM VARCHAR2(10) not null,
SUBJECT_JB NUMBER(19),
SUBJECT_PARENT NUMBER(19),
IF_DEL VARCHAR2(1) default '0' not null,
FP_SUBJECT VARCHAR2(50),
BB_SUBJECT VARCHAR2(50),
FYQD_SUBJECT VARCHAR2(50),
ORDER_NO NUMBER(3)
);
--药品表
create table BASIC_FEEITEM
(
ID NUMBER(19) not null,--药品id
CODE VARCHAR2(9) not null,
NAME VARCHAR2(200) not null,--药品名字
SNAME VARCHAR2(200),
ENAME VARCHAR2(200),
ISPAY NUMBER(19) not null,
SERVICE_TYPE_ID NUMBER(19) not null,
FINANCE_PAYTYPE_ID NUMBER(19),
PROJ_CONTENT VARCHAR2(2000),
EXCLV_CONTENT VARCHAR2(2000),
DESCRIPTION VARCHAR2(2000),
PRICE_UNIT NUMBER(19),
ISPUBLIC NUMBER(19),
GROUP_CATEGORY VARCHAR2(50),
INVOICE_TYPE_ID NUMBER(19),
STATISTIC_TYPE_ID NUMBER(19),
NUMERIC_CODE VARCHAR2(20),
PINYIN_CODE VARCHAR2(20),
WUBI_CODE VARCHAR2(20), SUBJECT_NO NUMBER(19)--药品分类
);
--处方明细表
create table PRE_CHARGE_RECIPE_D
(
ID NUMBER(19) not null,
RECIPE_NO NUMBER(19) not null,--处方编号
CF_IN_NO NUMBER(2) not null,
ZH NUMBER(2) not null,
BM NUMBER(19) not null,--药品编码
MC VARCHAR2(50) not null,
DJ NUMBER(7,2) not null,
ZJE NUMBER(9,2) not null,--总费用
IF_PS VARCHAR2(1) default '0',
IF_ZXZX VARCHAR2(1) default '0',
IF_ZB VARCHAR2(1) default '0',
TSYF VARCHAR2(100),
YBBXFY NUMBER(9,2),--医保费用
QTBXFY NUMBER(9,2)--其它报销费用
);输入查询条件为处方明细表中处方编号 RECIPE_NO一个处方号可能对应几个药品,根据药品的分类BASIC_FEEITEM.SUBJECT_NO(C_SUBJECT.ID),统计分类费用
如:
-----------------------------------
SUBJECT_NAME ZJE zifeijine
中药 20 12
西药 15 8-------------------------------
SUBJECT_NAME(C_SUBJECT.SUBJECT_NAME),ZJE(sum(PRE_CHARGE_RECIPE_D.zje)) zifeijine(sum(ZJE-YBBXFY-QTBXFY))
BASIC_FEEITEM.SUBJECT_NO=C_SUBJECT.ID
FROM C_SUBJECT C, BASIC_FEEITEM B, PRE_CHARGE_RECIPE_D P
WHERE P.BM = B.ID
AND B.SUBJECT_NO = C.ID
GROUP BY C.ID, C.SUBJECT_NAME;
select C_SUBJECT.SUBJECT_NAME,
sum(PRE_CHARGE_RECIPE_D.zje) ZJE,
sum(PRE_CHARGE_RECIPE_D.ZJE-PRE_CHARGE_RECIPE_D.YBBXFY-PRE_CHARGE_RECIPE_D.QTBXFY) zifeijine
from PRE_CHARGE_RECIPE_D,BASIC_FEEITEM,C_SUBJECT
where PRE_CHARGE_RECIPE_D.BM=BASIC_FEEITEM.ID and BASIC_FEEITEM.SUBJECT_NO=C_SUBJECT.ID
group by C_SUBJECT.SUBJECT_NAME
select C_SUBJECT.SUBJECT_NAME,sum(PRE_CHARGE_RECIPE_D.zje) zje,
sum(ZJE-YBBXFY-QTBXFY) zifeijine
from PRE_CHARGE_RECIPE_D,C_SUBJECT,BASIC_FEEITEM
where RECIPE_NO='xxxx' and
PRE_CHARGE_RECIPE_D.BM=BASIC_FEEITEM.ID and
BASIC_FEEITEM.SUBJECT_NO=C_SUBJECT.ID
group by C_SUBJECT.SUBJECT_NAME,BASIC_FEEITEM.SUBJECT_NO