TPerson 表， 部门 department，姓名 personname，职位 position
- SQL code
CREATE TABLE [TPerson] ( [Fid] [int] IDENTITY (1, 1) NOT NULL , [FPersonName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [FDepartmentId] [int] NULL CONSTRAINT [DF__TPerson__FDepart__412EB0B6] DEFAULT (0), [FPermissionId] [int] NULL CONSTRAINT [DF__TPerson__FPermis__4222D4EF] DEFAULT (0), [FPositionId] [int] NULL CONSTRAINT [DF__TPerson__FPositi__4316F928] DEFAULT (0), [FMachineAddr] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [FInvaild] [bit] NOT NULL CONSTRAINT [DF__TPerson__FInvail__440B1D61] DEFAULT (0), CONSTRAINT [aaaaaTPerson_PK] PRIMARY KEY NONCLUSTERED ( [Fid] ) ON [PRIMARY] ) ON [PRIMARY]GO
Department 部门表
- SQL code
CREATE TABLE [TDepartment] ( [Fid] [int] IDENTITY (1, 1) NOT NULL , [FDepartmentName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [FInvaild] [bit] NOT NULL CONSTRAINT [DF__TDepartme__FInva__0DAF0CB0] DEFAULT (0), CONSTRAINT [aaaaaTDepartment_PK] PRIMARY KEY NONCLUSTERED ( [Fid] ) ON [PRIMARY] ) ON [PRIMARY]GO
Postion 表， 职位表
- SQL code
CREATE TABLE [TPosition] ( [Fid] [int] IDENTITY (1, 1) NOT NULL , [FPositionName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [FInvaild] [bit] NOT NULL CONSTRAINT [DF__TPosition__FInva__48CFD27E] DEFAULT (0), CONSTRAINT [aaaaaTPosition_PK] PRIMARY KEY NONCLUSTERED ( [Fid] ) ON [PRIMARY] ) ON [PRIMARY]GO
具体项目表 TProductDetail
FCarryOfScore 获得分数 TPersonId 人员
- SQL code
CREATE TABLE [TProductDetail] ( [Fid] [int] IDENTITY (1, 1) NOT NULL , [FProductId] [int] NULL CONSTRAINT [DF__TProductD__FProd__5535A963] DEFAULT (0), [FPersonId] [int] NULL CONSTRAINT [DF__TProductD__FPers__5629CD9C] DEFAULT (0), [FProductLeader] [bit] NOT NULL CONSTRAINT [DF__TProductD__FProd__571DF1D5] DEFAULT (0), [FJobContent] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [FCarryOfScore] [money] NULL CONSTRAINT [DF__TProductD__FCarr__5812160E] DEFAULT (0), [FProjectId] [int] NULL CONSTRAINT [DF__TProductD__FProj__59063A47] DEFAULT (0), [FProjectLeader] [bit] NOT NULL CONSTRAINT [DF__TProductD__FProj__59FA5E80] DEFAULT (0), [FInvaild] [bit] NOT NULL CONSTRAINT [DF__TProductD__FInva__5AEE82B9] DEFAULT (0), CONSTRAINT [aaaaaTProductDetail_PK] PRIMARY KEY NONCLUSTERED ( [Fid] ) ON [PRIMARY] ) ON [PRIMARY]GO
扣分表DecreaseScore
FDecreaseScore 所扣分数 FPersonId 人员
- SQL code
CREATE TABLE [TDecreaseScore] ( [Fid] [int] IDENTITY (1, 1) NOT NULL , [FPersonId] [int] NULL CONSTRAINT [DF__TDecrease__FPers__060DEAE8] DEFAULT (0), [FDecreaseTime] [datetime] NULL , [FVaildTime] [datetime] NULL , [FReasonDetail] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL , [FDecreaseScore] [money] NULL CONSTRAINT [DF__TDecrease__FDecr__07020F21] DEFAULT (0), [FReasonId] [int] NULL CONSTRAINT [DF__TDecrease__FReas__07F6335A] DEFAULT (0), [FInvaild] [bit] NOT NULL CONSTRAINT [DF__TDecrease__FInva__08EA5793] DEFAULT (0), CONSTRAINT [aaaaaTDecreaseScore_PK] PRIMARY KEY NONCLUSTERED ( [Fid] ) ON [PRIMARY] ) ON [PRIMARY]GO
要求功能： 获取 人员姓名（FPersonId），所在部门（FDepartmentId），所在职位（TPositionId），获得分数（Sum（[FCarryOfScore] ） - sum（[FDecreaseScore]））