-- 员工表
CREATE TABLE [dbo].[EmpInfo](
[empId] [ int ] IDENTITY(1,1) NOT NULL ,
[empNo] [ varchar ](20) NULL ,
[empName] [nvarchar](20) NULL ,
CONSTRAINT [PK_EmpInfo] PRIMARY KEY CLUSTERED
(
[empId] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
-- 奖金表
CREATE TABLE [dbo].[SalaryInfo](
[id] [ int ] IDENTITY(1,1) NOT NULL ,
[empId] [ int ] NULL ,
[salary] [ decimal ](18, 2) NULL ,
[seasons] [ varchar ](20) NULL ,
CONSTRAINT [PK_SalaryInfo] PRIMARY KEY CLUSTERED
(
[id] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF
, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
-- 季度表
CREATE TABLE [dbo].[Seasons](
[ name ] [ nchar ](10) NULL
) ON [ PRIMARY ]
GO
SET IDENTITY_INSERT [dbo].[EmpInfo] ON
INSERT [dbo].[EmpInfo] ([empId], [empNo], [empName]) VALUES (1, N 'A001' , N '王强' )
INSERT [dbo].[EmpInfo] ([empId], [empNo], [empName]) VALUES (2, N 'A002' , N '李明' )
INSERT [dbo].[EmpInfo] ([empId], [empNo], [empName]) VALUES (3, N 'A003' , N '张三' )
INSERT [dbo].[SalaryInfo] ([id], [empId], [salary], [seasons])
VALUES (1, 1, CAST (3000.00 AS Decimal (18, 2)), N '第一季度' )
INSERT [dbo].[SalaryInfo] ([id], [empId], [salary], [seasons])
VALUES (2, 3, CAST (5000.00 AS Decimal (18, 2)), N '第一季度' )
INSERT [dbo].[SalaryInfo] ([id], [empId], [salary], [seasons])
VALUES (3, 1, CAST (3500.00 AS Decimal (18, 2)), N '第二季度' )
INSERT [dbo].[SalaryInfo] ([id], [empId], [salary], [seasons])
VALUES (4, 3, CAST (3000.00 AS Decimal (18, 2)), N '第二季度 ' )
INSERT [dbo].[SalaryInfo] ([id], [empId], [salary], [seasons])
VALUES (5, 2, CAST (4500.00 AS Decimal (18, 2)), N '第二季度' )
INSERT [dbo].[Seasons] ([ name ]) VALUES (N '第一季度' )
INSERT [dbo].[Seasons] ([ name ]) VALUES (N '第二季度' )
INSERT [dbo].[Seasons] ([ name ]) VALUES (N '第三季度' )
INSERT [dbo].[Seasons] ([ name ]) VALUES (N '第四季度' )
-- 查询每个人每个季度的奖金情况 如果奖金不存在则为0
SELECT a.empName,b. name seasons , isnull (c.salary,0) salary
FROM EmpInfo a
CROSS JOIN Seasons b
LEFT OUTER JOIN SalaryInfo c ON a.empId=c.empId AND b. name =c.seasons
|