1、
declare @t table([order] varchar(10))
insert @t select
'ABC00001' union all select
'ABC00002' union all select
'ABC00005' union all select
'ABC00006' union all select
'ABC00009' union all select
'CDA00002' union all select
'CDA00100' union all select
'CDA00101' union all select
'CDA00102' union all select
'BBB12345' union all select
'BBB12346' union all select
'BBB12347' union all select
'BBB12348' union all select
'BBB12349'
select top 20000 id=identity(int,1,1) into #t from sysobjects a,sysobjects b,sysobjects c
select le+right('0000'+ltrim(cast(right(mi,5)as int)+id),5) as [order]
from (
select left([order],3) as le ,max([order]) as ma,min([order]) as mi from @t
group by left([order],3)
)a,#t
where cast(right(mi,5)as int)+id< cast(right(ma,5)as int)
and le+right('0000'+ltrim(cast(right(mi,5)as int)+id),5) not in
(select [order] from @t)
order
----------------
ABC00003
ABC00004
ABC00007
ABC00008
CDA00003
CDA00004
CDA00005
。。。
--------------------------------------------------------------------------
2、
表中存放数据如下
ID DATE NUM
01 2009-01 10
01 2009-02 11
01 2009-03 12
02 2009-02 32
02 2009-04 33
即半年内的数据有就查询出来,没有对应的空缺月补0.
declare @t table(ID varchar(2),DATE varchar(7),NUM int)
insert @t select
'01', '2009-01' ,10 union all select
'01' ,'2009-02' ,11 union all select
'01' ,'2009-03' ,12 union all select
'02' ,'2009-02' , 32 union all select
'02' ,'2009-04', 33
select a.id,a.date,sum(isnull(NUM,0)) as num
from(
select distinct id,left(date,4)+'-'+right('0'+ltrim(number),2) as date
from @t ,master..spt_values b
where type='p' and number between 1 and 6
)a
left join @t t on a.date=t.date and a.id=t.id
group by a.id,a.date
order by a.id,a.date
id date num
---- ------------ -----------
01 2009-01 10
01 2009-02 11
01 2009-03 12
01 2009-04 0
01 2009-05 0
01 2009-06 0
02 2009-01 0
02 2009-02 32
02 2009-03 0
02 2009-04 33
02 2009-05 0
02 2009-06 0