paylist_v.sql

几个很好的SQL文件1供大家分享,觉得不错就下

USE Hospital
GO
CREATE VIEW PayList_v
AS
SELECT p.PayId, p.RegId,r.DocId,r.RegDate,
      m.MedName, b.TypeName, p.Price, m.Total, 
      p.PNum, p.Price * p.PNum AS PSum, m.BuyPrice, 
      (p.Price - m.BuyPrice) * p.PNum AS PEarn
FROM PayItems p, Medicine m, BaseType b,Registration r
WHERE p.ItemId = m.MedId AND m.UnitId = b.Id AND p.RegId=r.RegId AND p.PType=1
UNION
SELECT p.PayId, p.RegId,r.DocId, r.RegDate,i.ItemName, 
      b.TypeName, p.Price, i.Total, p.PNum, 
      p.Price * p.PNum AS PSum, i.BuyPrice, (p.Price - i.BuyPrice) 
      * p.PNum AS PEarn
FROM PayItems p, Items i, BaseType b,Registration r
WHERE p.ItemId = i.ItemId AND i.UnitId = b.Id AND p.RegId=r.RegId AND p.PType=2
GO