34,588
社区成员
发帖
与我相关
我的任务
分享
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
ID INT,
YearMonth INT
)
GO
INSERT INTO tba
SELECT 1, 201208 UNION
SELECT 2, 201208 UNION
SELECT 3, 201205 UNION
SELECT 1, 201207 UNION
SELECT 2, 201206 UNION
SELECT 3, 201204 UNION
SELECT 1, 201203 UNION
SELECT 2, 201204 UNION
SELECT 3, 201201
GO
SELECT DISTINCT ID,CAST(CONVERT(VARCHAR(6),DATEADD(MONTH,number,'20071101'),112) AS INT) AS existsMonth
FROM tba AS B, master..spt_values AS A
WHERE type = 'P' AND number >= 0 AND CAST(CONVERT(VARCHAR(6),DATEADD(MONTH,number,'20071101'),112) AS INT) NOT IN (SELECT YearMonth FROM tba WHERE B.ID = ID) AND CAST(CONVERT(VARCHAR(6),DATEADD(MONTH,number,'20071101'),112) AS INT) <= CAST(CONVERT(VARCHAR(6),GETDATE(),112) AS INT)
ORDER BY ID,CAST(CONVERT(VARCHAR(6),DATEADD(MONTH,number,'20071101'),112) AS INT)
/*
ID existsMonth
1 200711
1 200712
1 200801
1 200802
1 200803
1 200804
1 200805
1 200806
1 200807
1 200808
1 200809
1 200810
1 200811
1 200812
1 200901
1 200902
1 200903
1 200904
1 200905
1 200906
1 200907
1 200908
1 200909
1 200910
1 200911
1 200912
1 201001
1 201002
1 201003
1 201004
1 201005
1 201006
1 201007
1 201008
1 201009
1 201010
1 201011
1 201012
1 201101
1 201102
1 201103
1 201104
1 201105
1 201106
1 201107
1 201108
1 201109
1 201110
1 201111
1 201112
1 201201
1 201202
1 201204
1 201205
1 201206
1 201209
2 200711
2 200712
2 200801
2 200802
2 200803
2 200804
2 200805
2 200806
2 200807
2 200808
2 200809
2 200810
2 200811
2 200812
2 200901
2 200902
2 200903
2 200904
2 200905
2 200906
2 200907
2 200908
2 200909
2 200910
2 200911
2 200912
2 201001
2 201002
2 201003
2 201004
2 201005
2 201006
2 201007
2 201008
2 201009
2 201010
2 201011
2 201012
2 201101
2 201102
2 201103
2 201104
2 201105
2 201106
2 201107
2 201108
2 201109
2 201110
2 201111
2 201112
2 201201
2 201202
2 201203
2 201205
2 201207
2 201209
3 200711
3 200712
3 200801
3 200802
3 200803
3 200804
3 200805
3 200806
3 200807
3 200808
3 200809
3 200810
3 200811
3 200812
3 200901
3 200902
3 200903
3 200904
3 200905
3 200906
3 200907
3 200908
3 200909
3 200910
3 200911
3 200912
3 201001
3 201002
3 201003
3 201004
3 201005
3 201006
3 201007
3 201008
3 201009
3 201010
3 201011
3 201012
3 201101
3 201102
3 201103
3 201104
3 201105
3 201106
3 201107
3 201108
3 201109
3 201110
3 201111
3 201112
3 201202
3 201203
3 201206
3 201207
3 201208
3 201209
*/
-->try
declare @test table(A int,B int)
insert into @test
select 1, 201208 union all
select 2, 201208 union all
select 3, 201205 union all
select 1, 201207 union all
select 2, 201206 union all
select 3, 201204 union all
select 1, 201203 union all
select 2, 201204 union all
select 3, 201201
declare @ym int
set @ym=200711
select * from
(
select t.A,convert(varchar(6),dateadd(mm,number,ltrim(@ym)+'01'),112) dt
from master..spt_values,(select distinct A from @test) t
where type='P'
and number<=datediff(mm,ltrim(@ym)+'01',getdate())
) a
where not exists(select 1 from @test where B=a.dt and A=A.A)
order by A,dt
/*
A dt
----------- ------
1 200711
1 200712
1 200801
1 200802
1 200803
1 200804
1 200805
1 200806
1 200807
1 200808
1 200809
1 200810
1 200811
1 200812
1 200901
1 200902
1 200903
1 200904
1 200905
1 200906
1 200907
1 200908
1 200909
1 200910
1 200911
1 200912
1 201001
1 201002
1 201003
1 201004
1 201005
1 201006
1 201007
1 201008
1 201009
1 201010
1 201011
1 201012
1 201101
1 201102
1 201103
1 201104
1 201105
1 201106
1 201107
1 201108
1 201109
1 201110
1 201111
1 201112
1 201201
1 201202
1 201204
1 201205
1 201206
1 201209
2 200711
2 200712
2 200801
2 200802
2 200803
2 200804
2 200805
2 200806
2 200807
2 200808
2 200809
2 200810
2 200811
2 200812
2 200901
2 200902
2 200903
2 200904
2 200905
2 200906
2 200907
2 200908
2 200909
2 200910
2 200911
2 200912
2 201001
2 201002
2 201003
2 201004
2 201005
2 201006
2 201007
2 201008
2 201009
2 201010
2 201011
2 201012
2 201101
2 201102
2 201103
2 201104
2 201105
2 201106
2 201107
2 201108
2 201109
2 201110
2 201111
2 201112
2 201201
2 201202
2 201203
2 201205
2 201207
2 201209
3 200711
3 200712
3 200801
3 200802
3 200803
3 200804
3 200805
3 200806
3 200807
3 200808
3 200809
3 200810
3 200811
3 200812
3 200901
3 200902
3 200903
3 200904
3 200905
3 200906
3 200907
3 200908
3 200909
3 200910
3 200911
3 200912
3 201001
3 201002
3 201003
3 201004
3 201005
3 201006
3 201007
3 201008
3 201009
3 201010
3 201011
3 201012
3 201101
3 201102
3 201103
3 201104
3 201105
3 201106
3 201107
3 201108
3 201109
3 201110
3 201111
3 201112
3 201202
3 201203
3 201206
3 201207
3 201208
3 201209
*/
--try
SELECT b.[A],a.[B]
FROM (
SELECT CONVERT(VARCHAR(6) , DATEADD(mm , number , '20071101') , 112) AS [B]
FROM master..spt_values
WHERE type = 'p'
AND number BETWEEN 0 AND DATEDIFF(mm , '20071101' , GETDATE())
) a
CROSS JOIN (
SELECT [A] FROM [tb] GROUP BY [A]
) b
EXCEPT
SELECT * FROM [tb]