联合查询

客户提出打印费用流水帐的要求，要解决的问题是药费和处置费并不在一个表里边，而且字段名字也不相同，感觉解决办法很多。首先想到的是水晶报表的子报表功能。后来放弃了。因为这样比较麻烦。所以做了一个联合查询的视图：
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了。