客户提出打印费用流水帐的要求,要解决的问题是药费和处置费并不在一个表里边,而且字段名字也不相同,感觉解决办法很多。首先想到的是水晶报表的子报表功能。后来放弃了。因为这样比较麻烦。所以做了一个联合查询的视图:
alter view n_ihbill
as
SELECT CAST(ihformula.InPatientNo AS varchar) AS 住院号, i.Name AS 姓名, i.Age AS 年龄,
i.Sex AS 性别, i.Dept AS 科室, m.MName AS 项目名称, IHFormula.Quantity AS 数量,
IHFormula.tcost AS 金额, n_MedClass.type AS 类别,
IHFormula.FormulaTime AS 日期
FROM IHFormula LEFT OUTER JOIN
Medicine m ON m.MediNo = IHFormula.MediNo LEFT OUTER JOIN
ihpatientlist i ON IHFormula.InPatientNo = i.InPatientNo LEFT OUTER JOIN
n_MedClass ON m.MedClass = n_MedClass.MedClass
WHERE IHFormula.flgpay>0
UNION ALL
SELECT CAST(i.InPatientNo AS varchar) AS 住院号, i.Name AS 姓名, i.Age AS 年龄,
i.Sex AS 性别, i.Dept AS 科室, Payprice.Treat AS 项目名称, NULL AS 数量,
IHTreat.TCost AS 金额, Receipt_Item.Class AS 类别, IHTreat.TreatTime AS 日期
FROM ihpatientlist i INNER JOIN
IHTreat INNER JOIN
Payprice ON IHTreat.TrNo = Payprice.TrNo ON
i.InPatientNo = IHTreat.InPatientNo INNER JOIN
Receipt_Item ON Payprice.TrClsNo = Receipt_Item.ClsNo
这样做起来就easy了。
联合查询
最新推荐文章于 2022-10-22 23:39:28 发布