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

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

原创最新推荐文章于 2023-04-25 14:03:56 发布 · 595 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#date #insert #table #join

学习日记专栏收录该内容

27 篇文章

订阅专栏

本文介绍两种SQL数据处理方法：一是生成指定前缀未使用的订单编号；二是查询并填充半年内各月份的数据，若某月数据缺失，则以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