reportdoctor_v.sql

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

USE Hospital
GO
CREATE VIEW ReportDoctor_v
AS
SELECT d .Name AS 医生姓名, b.TypeName AS 科室名称, d .Title AS 职务, p.RegDate AS 月份,
      (CASE WHEN p.PNum >= 0 THEN p.PNum ELSE 0 END) AS 销售数量, 
      (CASE WHEN p.PSum >= 0 THEN p.PSum ELSE 0 END) AS 销售总额, 
      (CASE WHEN p.PEarn >= 0 THEN p.PEarn ELSE 0 END) AS 纯利润
FROM BaseType b, Doctor d,
          (SELECT DocId, LEFT(RegDate,7) AS RegDate, SUM(PNum) AS PNum, 
		SUM(PSum) AS PSum, SUM(PEarn) AS PEarn
         FROM PayList_v
         GROUP BY DocId, LEFT(RegDate,7)) p
WHERE b.TypeId = 1 AND d.DeptId = b.Id AND d.Id = p.DocId
GO