一个难题
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[employee]') AND type in (N'U'))
DROP TABLE [dbo].[employee]
GO
CREATE TABLE [dbo].[employee](
[pid] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[deptcode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[joindate] [datetime] NULL,
[quitdate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
go
insert into employee
select '1','depta','2012-03-29',null
union
select '2','depta','2012-06-11',null
union
select '3','deptb','2011-07-06',null
union
select '4','depta','2012-11-05','2013-02-02'
DECLARE @i DATETIME
SET @i ='2012-03-31'
SELECT MONTH(DATEADD (mm,A.number,@i)) AS 月份,B.*
FROM master..spt_values A
LEFT JOIN employee B ON MONTH(DATEADD (mm,A.number,@i)) = MONTH(B.joindate)
WHERE A.TYPE ='P' AND A.number <12
/*
月份piddeptcodejoindatequitdate
31depta2012-03-29 00:00:00.000NULL
4NULLNULLNULLNULL
5NULLNULLNULLNULL
62depta2012-06-11 00:00:00.000NULL
73deptb2011-07-06 00:00:00.000NULL
8NULLNULLNULLNULL
9NULLNULLNULLNULL
10NULLNULLNULLNULL
114depta2012-11-05 00:00:00.0002013-02-02
12NULLNULLNULLNULL
1NULLNULLNULLNULL
2NULLNULLNULLNULL*/