MSSQL2008为了方便测试我把建表的语句也放上来---人员列表
CREATE TABLE [dbo].[ry](
[cPsn_Num] [nvarchar](20) NOT NULL,
[cPsn_Name] [nvarchar](40) NOT NULL)Insert into ry([cPsn_Num],[cPsn_Name]) VALUES ('N001','张三')
Insert into ry([cPsn_Num],[cPsn_Name]) VALUES ('N002','李四')
Insert into ry([cPsn_Num],[cPsn_Name]) VALUES ('N003','王五')---表的内容
cPsn_Num cPsn_Name
N001 张三
N002 李四
N003 王五---工资数据表
CREATE TABLE [dbo].[gz](
[cPsn_Num] [nvarchar](20) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[bTFBZ] [bit] NULL,
[yf] [numeric](18, 2) NULL)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N001',2016,1,0,4150)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N001',2016,2,0,4250)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N001',2016,3,0,4190)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N002',2016,1,1,3180)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N002',2016,3,0,3200)---表的内容
cPsn_Num iYear iMonth bTFBZ yf
N001 2016 1 0 4150
N001 2016 2 0 4250
N001 2016 3 0 4190
N002 2016 1 1 3180
N002 2016 3 0 3200---公积金数据
CREATE TABLE [dbo].[gjj] (
[cPsn_Num] [nvarchar](20) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[gjj] [numeric](18, 2) NULL)Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N001',2016,2, 150)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N001',2016,3, 150)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N002',2016,1, 300)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N002',2016,2, 300)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N002',2016,3, 330)---表的内容
cPsn_Num iYear iMonth gjj
N001 2016 2 150
N001 2016 3 150
N002 2016 1 300
N002 2016 2 300
N002 2016 3 330
--医疗保险数据
CREATE TABLE [dbo].[yb](
[cPsn_Num] [nvarchar](10) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[yb] [numeric](18, 2) NULL,
[db] [numeric](18, 2) NULL,
[gwy] [numeric](18, 2) NULL)Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N002',2016,2,130,15,0)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N002',2016,3,180,19,60)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N002',2016,3,0,5,50)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N001',2016,1,160,55,80)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N001',2016,2,160,55,80)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N001',2016,3,190,0,30)---表的内容
cPsn_Num iYear iMonth yb db gwy
N002 2016 2 130 15 0
N002 2016 3 180 19 60
N001 2016 1 160 55 80
N001 2016 2 160 55 80
N001 2016 3 190 0 30
N002 2016 3 0 5 50---养老保险数据
CREATE TABLE [dbo].[yl](
[cPsn_Num] [nvarchar](10) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[yl] [numeric](18, 2) NULL,
[gs] [numeric](18, 2) NULL,
[sy] [numeric](18, 2) NULL)Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,1,690,30,20)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,1,30,0,0)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,2,680,130,60)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,4,690,80,20)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N002',2016,1,590,60,40)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N002',2016,2,580,180,70)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N002',2016,3,590,70,40)
---表的内容
cPsn_Num iYear iMonth yl gs sy
N001 2016 1 690 30 20
N001 2016 2 680 130 60
N001 2016 4 690 80 20
N002 2016 1 590 60 40
N002 2016 2 580 180 70
N002 2016 3 590 70 40
N001 2016 1 30 0 0需要建立视图的字段
Ry.cPsn_Num,Ry.[cPsn_Name],gz.cDept_Num,iyear,imonth,gz.yf,gjj.gjj, yb.yb,yb.db,yb.gwy,yl.yl,yl.gs,yl.sy
数值部分（gz.yf,gjj.gjj, yb.yb,yb.db,yb.gwy,yl.yl,yl.gs,yl.sy）需根据cPsn_Num,iyear,imonth汇总cPsn_Num说明
由ry表取得，只要ry表中存在均显示，其他表中无数据的显示为0
iyear,imonth说明
根据gz\gjj\yb\yl表中存在的值，只要一张表中有值就需显示，其他表没有的数值显示为零工资表过滤规则
Where gz.bTFBZ=0表间inner join 关联
Ry.cPsn_Num=gz.cPsn_Num=gjj.cPsn_Num=yb.cPsn_Num=yl.cPsn_Num
Gz.iyear=gjj.iyear=yb.iyear=yl.iyear
Gz.imonth=gjj.imonth =yb.imonth =yl.imonth
正确的结果：
cPsn_Num cPsn_Name iYear iMonth gjj yf yb db gwy yl gs sy
N001 张三 2016 1 0 4150 160 55 80 720 30 20
N002 李四 2016 1 300 0 0 0 0 590 60 40
N003 王五 2016 1 0 0 0 0 0 0 0 0
N001 张三 2016 2 150 4250 160 55 80 680 130 60
N002 李四 2016 2 300 0 130 15 0 580 180 70
N003 王五 2016 2 0 0 0 0 0 0 0 0
N001 张三 2016 3 150 4190 190 0 30 0 0 0
N002 李四 2016 3 330 3200 180 24 110 590 70 40
N003 王五 2016 3 0 0 0 0 0 0 0 0
N001 张三 2016 4 0 0 0 0 0 690 80 20
N002 李四 2016 4 0 0 0 0 0 0 0 0
N003 王五 2016 4 0 0 0 0 0 0 0 0
求上边这个查询结果的脚本，是有点复杂，可能需要一点时间看懂意图，我弄了两天都不对，谢谢了
CREATE TABLE [dbo].[ry](
[cPsn_Num] [nvarchar](20) NOT NULL,
[cPsn_Name] [nvarchar](40) NOT NULL)Insert into ry([cPsn_Num],[cPsn_Name]) VALUES ('N001','张三')
Insert into ry([cPsn_Num],[cPsn_Name]) VALUES ('N002','李四')
Insert into ry([cPsn_Num],[cPsn_Name]) VALUES ('N003','王五')---表的内容
cPsn_Num cPsn_Name
N001 张三
N002 李四
N003 王五---工资数据表
CREATE TABLE [dbo].[gz](
[cPsn_Num] [nvarchar](20) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[bTFBZ] [bit] NULL,
[yf] [numeric](18, 2) NULL)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N001',2016,1,0,4150)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N001',2016,2,0,4250)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N001',2016,3,0,4190)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N002',2016,1,1,3180)
Insert into gz([cPsn_Num], [iYear],[iMonth], [bTFBZ],[yf]) VALUES ('N002',2016,3,0,3200)---表的内容
cPsn_Num iYear iMonth bTFBZ yf
N001 2016 1 0 4150
N001 2016 2 0 4250
N001 2016 3 0 4190
N002 2016 1 1 3180
N002 2016 3 0 3200---公积金数据
CREATE TABLE [dbo].[gjj] (
[cPsn_Num] [nvarchar](20) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[gjj] [numeric](18, 2) NULL)Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N001',2016,2, 150)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N001',2016,3, 150)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N002',2016,1, 300)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N002',2016,2, 300)
Insert into gjj([cPsn_Num], [iYear],[iMonth], [gjj]) VALUES ('N002',2016,3, 330)---表的内容
cPsn_Num iYear iMonth gjj
N001 2016 2 150
N001 2016 3 150
N002 2016 1 300
N002 2016 2 300
N002 2016 3 330
--医疗保险数据
CREATE TABLE [dbo].[yb](
[cPsn_Num] [nvarchar](10) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[yb] [numeric](18, 2) NULL,
[db] [numeric](18, 2) NULL,
[gwy] [numeric](18, 2) NULL)Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N002',2016,2,130,15,0)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N002',2016,3,180,19,60)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N002',2016,3,0,5,50)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N001',2016,1,160,55,80)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N001',2016,2,160,55,80)
Insert into yb([cPsn_Num],[iYear],[iMonth],[yb],[db],[gwy]) VALUES ('N001',2016,3,190,0,30)---表的内容
cPsn_Num iYear iMonth yb db gwy
N002 2016 2 130 15 0
N002 2016 3 180 19 60
N001 2016 1 160 55 80
N001 2016 2 160 55 80
N001 2016 3 190 0 30
N002 2016 3 0 5 50---养老保险数据
CREATE TABLE [dbo].[yl](
[cPsn_Num] [nvarchar](10) NOT NULL,
[iYear] [int] NOT NULL,
[iMonth] [tinyint] NOT NULL,
[yl] [numeric](18, 2) NULL,
[gs] [numeric](18, 2) NULL,
[sy] [numeric](18, 2) NULL)Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,1,690,30,20)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,1,30,0,0)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,2,680,130,60)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N001',2016,4,690,80,20)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N002',2016,1,590,60,40)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N002',2016,2,580,180,70)
Insert into yl([cPsn_Num],[iYear],[iMonth],[yl],[gs],[sy]) VALUES ('N002',2016,3,590,70,40)
---表的内容
cPsn_Num iYear iMonth yl gs sy
N001 2016 1 690 30 20
N001 2016 2 680 130 60
N001 2016 4 690 80 20
N002 2016 1 590 60 40
N002 2016 2 580 180 70
N002 2016 3 590 70 40
N001 2016 1 30 0 0需要建立视图的字段
Ry.cPsn_Num,Ry.[cPsn_Name],gz.cDept_Num,iyear,imonth,gz.yf,gjj.gjj, yb.yb,yb.db,yb.gwy,yl.yl,yl.gs,yl.sy
数值部分（gz.yf,gjj.gjj, yb.yb,yb.db,yb.gwy,yl.yl,yl.gs,yl.sy）需根据cPsn_Num,iyear,imonth汇总cPsn_Num说明
由ry表取得，只要ry表中存在均显示，其他表中无数据的显示为0
iyear,imonth说明
根据gz\gjj\yb\yl表中存在的值，只要一张表中有值就需显示，其他表没有的数值显示为零工资表过滤规则
Where gz.bTFBZ=0表间inner join 关联
Ry.cPsn_Num=gz.cPsn_Num=gjj.cPsn_Num=yb.cPsn_Num=yl.cPsn_Num
Gz.iyear=gjj.iyear=yb.iyear=yl.iyear
Gz.imonth=gjj.imonth =yb.imonth =yl.imonth
正确的结果：
cPsn_Num cPsn_Name iYear iMonth gjj yf yb db gwy yl gs sy
N001 张三 2016 1 0 4150 160 55 80 720 30 20
N002 李四 2016 1 300 0 0 0 0 590 60 40
N003 王五 2016 1 0 0 0 0 0 0 0 0
N001 张三 2016 2 150 4250 160 55 80 680 130 60
N002 李四 2016 2 300 0 130 15 0 580 180 70
N003 王五 2016 2 0 0 0 0 0 0 0 0
N001 张三 2016 3 150 4190 190 0 30 0 0 0
N002 李四 2016 3 330 3200 180 24 110 590 70 40
N003 王五 2016 3 0 0 0 0 0 0 0 0
N001 张三 2016 4 0 0 0 0 0 690 80 20
N002 李四 2016 4 0 0 0 0 0 0 0 0
N003 王五 2016 4 0 0 0 0 0 0 0 0
求上边这个查询结果的脚本，是有点复杂，可能需要一点时间看懂意图，我弄了两天都不对，谢谢了
N002 2016 3 180 19 60
N002 2016 3 0 5 50如果这里是书写错误，每个月仅有一条，你可以试试下面的语句select Ry.cPsn_Num,ry.cPsn_Name,YEAR(GETDATE()),sv.number,
sum(isnull(gjj.gjj,0)) as gjj,sum(isnull(gz.yf,0)) as yf, sum(isnull(yb.yb,0)) as yb,sum(isnull(yb.db,0)) as db,sum(isnull(yb.gwy,0)) as gwy
,sum(isnull(yl.yl,0)) as yl,sum(isnull(yl.gs,0)) as gs,sum(isnull(yl.sy,0)) as sy
FROM ry
INNER JOIN master.dbo.spt_values as sv on sv.type='P' and sv.number>0
LEFT JOIN gz ON Ry.cPsn_Num=gz.cPsn_Num and gz.bTFBZ=0 and gz.imonth=sv.number AND gz.iYear=YEAR(GETDATE())
LEFT JOIN gjj ON Ry.cPsn_Num=gjj.cPsn_Num AND YEAR(GETDATE())=gjj.iyear AND sv.number=gjj.imonth
left JOIN yb ON Ry.cPsn_Num=yb.cPsn_Num AND YEAR(GETDATE())=yb.iyear AND sv.number=yb.imonth
LEFT JOIN yl ON Ry.cPsn_Num=yl.cPsn_Num AND YEAR(GETDATE())=yl.iyear AND sv.number=yl.imonth
where sv.number<MONTH(GETDATE())
GROUP BY Ry.cPsn_Num,ry.cPsn_Name,sv.number
ORDER BY sv.number,Ry.cPsn_Num
如果每个月会存在多种，那么可以先将小表的数据线按照年月合计后再关联
master..spt_values where type='p' 这个查了资料看懂了
但有个问题，示例数据只有2016年的，所以楼上的直接用了YEAR(GETDATE())，但实际情况是会有多年的。
我自己建个表，把2010到2020添加上，仿照着把iyear这个改了下，最后的10行 where sv.number<MONTH(GETDATE()) 改成 where sv.number<12
虽说基本上解决了问题，但感觉还是不太完美，有没有更好的方法望不吝赐教