请教一个简单的有关遗漏日期的问题
列A 列B
1 201208
2 201208
3 201205
1 201207
2 201206
3 201204
1 201203
2 201204
3 201201
求教，B列录入的时候是输入的int型，我想把它作为日期形式，能不能写条句子查询出列A为1、2、3从200711到现在遗漏的日期。谢谢大神们！ 就比如列A为1的 201207-201203就缺了3个月~
[解决办法]
- SQL code
--trySELECT 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()) ) aCROSS JOIN ( SELECT [A] FROM [tb] GROUP BY [A] ) bEXCEPT SELECT * FROM [tb]
[解决办法]
- SQL code
-->trydeclare @test table(A int,B int)insert into @testselect 1, 201208 union allselect 2, 201208 union all select 3, 201205 union allselect 1, 201207 union allselect 2, 201206 union allselect 3, 201204 union allselect 1, 201203 union allselect 2, 201204 union allselect 3, 201201declare @ym intset @ym=200711select * 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())) awhere not exists(select 1 from @test where B=a.dt and A=A.A)order by A,dt/*A dt----------- ------1 2007111 2007121 2008011 2008021 2008031 2008041 2008051 2008061 2008071 2008081 2008091 2008101 2008111 2008121 2009011 2009021 2009031 2009041 2009051 2009061 2009071 2009081 2009091 2009101 2009111 2009121 2010011 2010021 2010031 2010041 2010051 2010061 2010071 2010081 2010091 2010101 2010111 2010121 2011011 2011021 2011031 2011041 2011051 2011061 2011071 2011081 2011091 2011101 2011111 2011121 2012011 2012021 2012041 2012051 2012061 2012092 2007112 2007122 2008012 2008022 2008032 2008042 2008052 2008062 2008072 2008082 2008092 2008102 2008112 2008122 2009012 2009022 2009032 2009042 2009052 2009062 2009072 2009082 2009092 2009102 2009112 2009122 2010012 2010022 2010032 2010042 2010052 2010062 2010072 2010082 2010092 2010102 2010112 2010122 2011012 2011022 2011032 2011042 2011052 2011062 2011072 2011082 2011092 2011102 2011112 2011122 2012012 2012022 2012032 2012052 2012072 2012093 2007113 2007123 2008013 2008023 2008033 2008043 2008053 2008063 2008073 2008083 2008093 2008103 2008113 2008123 2009013 2009023 2009033 2009043 2009053 2009063 2009073 2009083 2009093 2009103 2009113 2009123 2010013 2010023 2010033 2010043 2010053 2010063 2010073 2010083 2010093 2010103 2010113 2010123 2011013 2011023 2011033 2011043 2011053 2011063 2011073 2011083 2011093 2011103 2011113 2011123 2012023 2012033 2012063 2012073 2012083 201209*/
[解决办法]
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( ID INT, YearMonth INT)GOINSERT INTO tbaSELECT 1, 201208 UNIONSELECT 2, 201208 UNIONSELECT 3, 201205 UNIONSELECT 1, 201207 UNIONSELECT 2, 201206 UNIONSELECT 3, 201204 UNIONSELECT 1, 201203 UNIONSELECT 2, 201204 UNIONSELECT 3, 201201GOSELECT DISTINCT ID,CAST(CONVERT(VARCHAR(6),DATEADD(MONTH,number,'20071101'),112) AS INT) AS existsMonthFROM tba AS B, master..spt_values AS AWHERE 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 existsMonth1 2007111 2007121 2008011 2008021 2008031 2008041 2008051 2008061 2008071 2008081 2008091 2008101 2008111 2008121 2009011 2009021 2009031 2009041 2009051 2009061 2009071 2009081 2009091 2009101 2009111 2009121 2010011 2010021 2010031 2010041 2010051 2010061 2010071 2010081 2010091 2010101 2010111 2010121 2011011 2011021 2011031 2011041 2011051 2011061 2011071 2011081 2011091 2011101 2011111 2011121 2012011 2012021 2012041 2012051 2012061 2012092 2007112 2007122 2008012 2008022 2008032 2008042 2008052 2008062 2008072 2008082 2008092 2008102 2008112 2008122 2009012 2009022 2009032 2009042 2009052 2009062 2009072 2009082 2009092 2009102 2009112 2009122 2010012 2010022 2010032 2010042 2010052 2010062 2010072 2010082 2010092 2010102 2010112 2010122 2011012 2011022 2011032 2011042 2011052 2011062 2011072 2011082 2011092 2011102 2011112 2011122 2012012 2012022 2012032 2012052 2012072 2012093 2007113 2007123 2008013 2008023 2008033 2008043 2008053 2008063 2008073 2008083 2008093 2008103 2008113 2008123 2009013 2009023 2009033 2009043 2009053 2009063 2009073 2009083 2009093 2009103 2009113 2009123 2010013 2010023 2010033 2010043 2010053 2010063 2010073 2010083 2010093 2010103 2010113 2010123 2011013 2011023 2011033 2011043 2011053 2011063 2011073 2011083 2011093 2011103 2011113 2011123 2012023 2012033 2012063 2012073 2012083 201209*/