sql.txt

还不错

CREATE DATABASE Hospital
GO


USE Hospital
GO
CREATE TABLE BaseType
  (Id  	int PRIMARY KEY IDENTITY,
   TypeId   	tinyint,
   TypeName 	varchar(30)
  )
GO


USE Hospital
GO
CREATE TABLE Doctor
   ( Id	int IDENTITY PRIMARY KEY,
   Name		varchar(50) NOT NULL,
   Sex     	varchar(2) NOT NULL,
   Age     	int,
   DeptId  	int,
   Title   	varchar(50)
);
GO


USE Hospital
GO
CREATE TABLE Items
  ( ItemId    	int IDENTITY PRIMARY KEY,
   ItemName  	varchar(50) NOT NULL,
   Total     	int,
   UnitId    	int,
   BuyPrice  	float,
   SalePrice 	float
)
GO


USE Hospital
GO
CREATE TABLE Medicine
   ( MedId    	int IDENTITY PRIMARY KEY,
   MedName  	varchar(50),
   TypeId   	int,
   UnitId   	int,
   BuyPrice  	float,      --进货价格，计算利润用
   SalePrice 	float,      --售出价格
   Total     	float,
   Flag      	tinyint     --中西药标记，0-中药；1-西药
)
GO



USE Hospital
GO
CREATE TABLE Patient
   ( Id      	int IDENTITY PRIMARY KEY,
   Name    	varchar(50),
   IDNum   	varchar(30),
   Sex     	varchar(2),
   Age     	int,
   Marry   	varchar(8)
)
GO




USE Hospital
GO
CREATE TABLE PayItems
  ( PayId   	int IDENTITY PRIMARY KEY,
   RegId   	int,  -- 登记编号
   ItemId  	int,  -- 收费项目编号
   PNum    	int,  -- 收费数量
   Price   	float,-- 销售价格
   PType   	tinyint -- 类型（1-药品，2-项目）
)
GO


USE Hospital
GO
CREATE TABLE Registration
  ( RegId   	int PRIMARY KEY,
   PatId   	int,--患者
   DocId   	int,  --医生名称
   RegDate 	varchar(20),   --日期
   UserName 	varchar(20) --用户名
)
GO



USE Hospital
GO
CREATE TABLE Users
  (UserName  	varchar(40) PRIMARY KEY,
   Pwd	varchar(40) NOT NULL,
   User_Type	tinyint NOT NULL
  )
GO
INSERT INTO Users Values('Admin', '888888', 1)
GO


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



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






USE Hospital
GO
CREATE VIEW ReportItems_v
AS
SELECT i.ItemId AS 药品编号, i.ItemName AS 药品名称, b.TypeName AS 单位, 
      i.BuyPrice AS 成本价格, i.SalePrice AS 销售价格, i.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 - i.BuyPrice * p.PNum) ELSE 0 END) 
      AS 纯利润
FROM Items i, BaseType b,
          (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 = 2 AND ps.RegId = r.RegId
         GROUP BY ps.ItemId, ps.RegId, LEFT(r.RegDate,7)) p
WHERE i.ItemId = p.ItemId AND i.UnitId = b.Id
GO


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