reportmedicine_v.sql

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

USE Hospital
GO
CREATE VIEW ReportMedicine_v
AS
SELECT m.MedId AS 药品编号, m.MedName AS 药品名称, 
      (CASE WHEN m.Flag = 1 THEN '中药' ELSE '西药' END) AS 类别, 
      b1.TypeName AS 分类, b.TypeName AS 单位, m.BuyPrice AS 进货价格, 
      m.SalePrice AS 销售价格, m.Total AS 库存数量, p.RegDate AS 销售日期,
      (CASE WHEN p.PNum >= 0 THEN p.PNum ELSE 0 END) AS 销售数量, 
      (CASE WHEN p.PNum >= 0 THEN p.PSum ELSE 0 END) AS 销售总额, 
      (CASE WHEN p.PNum >= 0 THEN (p.PSum - m.BuyPrice * p.PNum) ELSE 0 END) 
      AS 纯利润
FROM Medicine m, BaseType b, BaseType b1,
          (SELECT ps.ItemId, ps.RegId, LEFT(r.RegDate,7) AS RegDate, SUM(ps.PNum) AS PNum, 
               SUM(ps.Price * ps.PNum) AS PSum
         FROM PayItems ps, Registration r
         WHERE ps.PType = 1 AND ps.RegId = r.RegId
         GROUP BY ps.ItemId, ps.RegId, LEFT(r.RegDate,7)) p
WHERE m.MedId = p.ItemId AND m.UnitId = b.Id AND m.TypeId = b1.Id
GO