sql server 2008 r2-在sql中使用cte创建范围

Question

我有一个只有单列的表，其值如下

declare @numberrange table 
(
  num int 
)
insert into @numberrange (num) 
 values(24) ,
 (29) ,
( 34 ),
( 39 ),
( 44 ),
( 49 ),
( 54 ),
( 59  ),
( 64  ),
( 69  ),
( 74  ),
( 99 )

现在我想显示如下的结果

24  24-29
29  30-34
34  35-39
39  40-44
44  45-49
49  50-54
54  55-59
59  60-64
64  65-69
69  70-74
74  75-99
99

我试过使用sql-cte函数，但没能得到想要的结果。

Answer 1

您可以使用此CTE查询。查询从最后一条记录构建范围，以便最后一条有空范围，而第一条有范围。

;with cte
as
(
    select top 1 num, sortorder, convert(varchar(21), null) rangevalue
    from (select num, ROW_NUMBER() over (order by num) as sortorder from @numberrange) x
    order by sortorder desc
    union all
    select x.num, x.sortorder, convert(varchar(10), x.num) + '-' + convert(varchar(10), cte.num)
    from (select num, ROW_NUMBER() over (order by num) as sortorder from @numberrange) x
    inner join cte
    on cte.sortorder = x.sortorder + 1
)
select num, rangevalue from cte
order by num