TPerson 表, 部门 department,姓名 personname,职位 position
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]
GODepartment 部门表
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 表, 职位表
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 人员
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 人员
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])) 对每隔 FPersonId 进行分组。但是我写的如下 SQL 却没有实现有很多 NULL.
我的错误的SQL
SELECT pd.FPersonId, p.FDepartmentId, p.FPositionId, (SUM(pd.FCarryOfScore) - (SELECT SUM(FDecreaseScore) from TDecreaseScore where FPersonid = pd.FPersonid)) as sum
FROM TProductDetail pd
INNER JOIN TPerson p
ON p.fid = pd.FpersonId
WHERE pd.FInvaild = false
GROUP BY FPersonId, FDepartmentId, FPositionId求改怎么做?
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]
GODepartment 部门表
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 表, 职位表
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 人员
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 人员
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])) 对每隔 FPersonId 进行分组。但是我写的如下 SQL 却没有实现有很多 NULL.
我的错误的SQL
SELECT pd.FPersonId, p.FDepartmentId, p.FPositionId, (SUM(pd.FCarryOfScore) - (SELECT SUM(FDecreaseScore) from TDecreaseScore where FPersonid = pd.FPersonid)) as sum
FROM TProductDetail pd
INNER JOIN TPerson p
ON p.fid = pd.FpersonId
WHERE pd.FInvaild = false
GROUP BY FPersonId, FDepartmentId, FPositionId求改怎么做?
,Isnull(FCarryOfScore,0) -Isnull(FDecreaseScore,0) as '获得分数'
from TPerson P
inner join Department D on P.FDepartmentId=D.Fid
Inner Join TPosition PS ON P.FPositionId=PS.Fid
Left JOIN (Select FPersonId ,sum(FCarryOfScore) as FCarryOfScore from TProductDetail group by FPersonId ) G ON G.FPersonId =P.Fid
Left JOIN (select FPersonId,sum(FDecreaseScore) as FDecreaseScore from TDecreaseScore group by FPersonId) DE ON DE.FPersonId=P.Fid AND DE.FPersonId=G.FPersonId