一个关于SQL语句的菜鸟问题

lakehubo 2006-06-29 09:52:57

俺是做HIS 的俺想做一个统计药房发西药中成药的报表
为了区分药品的中成药＆西药就得关联价表在关联价表的时候会出现条件的不唯一性
因为在价表中关于一条药品的价格可能有多条记录(就是有的药品换了厂家或者调价,就产生一条新的价格记录,原来的记录坐上停价标志)
下面是我的SQL
SELECT
'住院药房' DEPT,
SUM(COSTS),
SUM(WESTDRUG) WEST,
SUM(CHMDRUG) CHINE,
SUM(SELFDRUG) SELF
FROM
(
SELECT //医嘱摆药
SUM(DRUG_DISPENSE_REC.COSTS) COSTS,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A01',DRUG_DISPENSE_REC.COSTS,0)),2) WESTDRUG,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A02',DRUG_DISPENSE_REC.COSTS,0)),2) CHMDRUG,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A04',DRUG_DISPENSE_REC.COSTS,0)),2) SELFDRUG
FROM
DRUG_PRICE_LIST,
DRUG_DISPENSE_REC
WHERE
DRUG_PRICE_LIST.DRUG_CODE = DRUG_DISPENSE_REC.DRUG_CODE AND
DRUG_PRICE_LIST.DRUG_SPEC = DRUG_DISPENSE_REC.DRUG_SPEC AND
DRUG_PRICE_LIST.UNITS = DRUG_DISPENSE_REC.DRUG_UNITS AND
DRUG_PRICE_LIST.FIRM_ID = DRUG_DISPENSE_REC.FIRM_ID AND
DRUG_DISPENSE_REC.DISPENSARY = '5010' AND
DRUG_PRICE_LIST.STOP_DATE IS NULL AND //就是这里 !!!!!
TO_CHAR(DRUG_DISPENSE_REC.DISPENSING_DATE_TIME,'YYYYMMDD')>='20060526' AND
TO_CHAR(DRUG_DISPENSE_REC.DISPENSING_DATE_TIME,'YYYYMMDD')<='20060625'
UNION ALL
SELECT //处方摆药
SUM(DRUG_PRESC_DETAIL.COSTS) COSTS,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A01',DRUG_PRESC_DETAIL.COSTS,0)),2) WESTDRUG,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A02',DRUG_PRESC_DETAIL.COSTS,0)),2) CHMDRUG,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A04',DRUG_PRESC_DETAIL.COSTS,0)),2) SELFDRUG
FROM
DRUG_PRESC_MASTER,
DRUG_PRESC_DETAIL,
DRUG_PRICE_LIST
WHERE
DRUG_PRESC_MASTER.PRESC_NO = DRUG_PRESC_DETAIL.PRESC_NO AND
DRUG_PRESC_MASTER.PRESC_DATE = DRUG_PRESC_DETAIL.PRESC_DATE AND
DRUG_PRESC_MASTER.PRESC_SOURCE = '1' AND
DRUG_PRESC_MASTER.DISPENSARY = '5010' AND
DRUG_PRICE_LIST.STOP_DATE IS NULL AND //还有这里!!!
DRUG_PRESC_DETAIL.DRUG_CODE = DRUG_PRICE_LIST.DRUG_CODE AND
DRUG_PRESC_DETAIL.PACKAGE_UNITS = DRUG_PRICE_LIST.UNITS AND
DRUG_PRESC_DETAIL.FIRM_ID = DRUG_PRICE_LIST.FIRM_ID AND
DRUG_PRESC_DETAIL.PACKAGE_SPEC = DRUG_PRICE_LIST.DRUG_SPEC AND
TO_CHAR(DRUG_PRESC_MASTER.DISPENSING_DATETIME,'YYYYMMDD')>='20060526' AND
TO_CHAR(DRUG_PRESC_MASTER.DISPENSING_DATETIME,'YYYYMMDD')<='20060625'
UNION ALL
SELECT //药品出库
SUM(DRUG_EXPORT_DETAIL.RETAIL_PRICE * DRUG_EXPORT_DETAIL.QUANTITY) COSTS,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A01',DRUG_EXPORT_DETAIL.RETAIL_PRICE * DRUG_EXPORT_DETAIL.QUANTITY,0)),2) WESTDRUG,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A02',DRUG_EXPORT_DETAIL.RETAIL_PRICE * DRUG_EXPORT_DETAIL.QUANTITY,0)),2) CHMDRUG,
ROUND(SUM(DECODE(DRUG_PRICE_LIST.CLASS_ON_RECKONING,'A04',DRUG_EXPORT_DETAIL.RETAIL_PRICE * DRUG_EXPORT_DETAIL.QUANTITY,0)),2) SELFDRUG
FROM
DRUG_EXPORT_MASTER ,
DRUG_EXPORT_DETAIL,
DRUG_PRICE_LIST
WHERE
DRUG_EXPORT_MASTER.DOCUMENT_NO = DRUG_EXPORT_DETAIL.DOCUMENT_NO AND
DRUG_EXPORT_MASTER.STORAGE = '5010' AND
DRUG_PRICE_LIST.STOP_DATE IS NULL AND //这里
DRUG_EXPORT_DETAIL.DRUG_CODE = DRUG_PRICE_LIST.DRUG_CODE AND
DRUG_EXPORT_DETAIL.DRUG_SPEC = DRUG_PRICE_LIST.DRUG_SPEC AND
DRUG_EXPORT_DETAIL.UNITS = DRUG_PRICE_LIST.UNITS AND
DRUG_EXPORT_DETAIL.FIRM_ID = DRUG_PRICE_LIST.FIRM_ID AND
TO_CHAR(DRUG_EXPORT_MASTER.EXPORT_DATE,'YYYYMMDD')<='20060625' AND
TO_CHAR(DRUG_EXPORT_MASTER.EXPORT_DATE,'YYYYMMDD')>='20060526' AND
DRUG_EXPORT_MASTER.RECEIVER NOT LIKE '住院药房'
);
我现在用的方法是取停价日期为空的那一条不过不能保证价表中去到的药＆药房发的药是同一厂家
(存在药品换厂家的可能)
我现在想的一个办法就是取价表中价格开始时间(START_DATE) 为价表中记录的药品＆药房发药所记录的同一药品同一厂家中的价表中的所有记录的最大值,
哪位大虾指点一下啊
感激不尽!!

...全文