利用master.dbo.spt_values左连接，取连续记录

最新推荐文章于 2023-04-25 14:03:56 发布

华夏小卒

最新推荐文章于 2023-04-25 14:03:56 发布

阅读量545

点赞数

分类专栏：学习日记文章标签： date insert table join

本文链接：https://blog.csdn.net/js_szy/article/details/4582083

版权

学习日记专栏收录该内容

27 篇文章 0 订阅

订阅专栏

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