【SQLServer】不需要递迴,使用spt_values快速生成连续整数0-2048

今天想回答sql server - Select Consecutive Numbers in SQL爬文发现,自己居然一直不知道spt_values用法 (羞愧Orz..),所以写文章分享给不小心不知道的版友。

连续整数是一个常见的SQL需求,而SQL-Server的开发者除了递迴的方式外,其实还有一个方式能解决需求,并且效率更好。

举例 :
在S.O想要知道某个帐号在24小时每小时发文comment总次数,来研究使用的时间利用情况,如下图,我们能清楚了解该用户在2-9点期间都是在休息的。

小时总评论次数14972030405060708092710140113111211133861424215318161931742182611942320862136422132232232495

因为我们都知道S.O使用的是SQL-Server资料库,通常第一直觉作法是使用recursive CTE(递迴)解决

e.g:

with cte_hours as (    select 1 as hour    union all    select hour + 1    from cte_hours    where hour < 24)select h.hour ,c.countfrom cte_hours h left join (    select datepart(hour, creationdate) as hour,count(1) count    from comments    where userid = '9131476'    group by datepart(hour, creationdate)) c on h.hour = c.hourorder by h.hour;

online demo link : consecutive number query demo - Stack Exchange Data Explorer

但这样这会感觉杀鸡用牛刀,并且递迴数量一大效率问题就会出现。

其实在master有一个表格spt_values我们可以拿来利用,原因是它本身有一个栏位number就是现成的连续整数,可以将SQL改成以下这样 :

select h.hour ,c.countfrom (    select top 24 number + 1 as hour from master..spt_values     where type = 'P') h left join (    select datepart(hour, creationdate) as hour,count(1) count    from comments    where userid = '9131476'    group by datepart(hour, creationdate)) c on h.hour = c.hourorder by h.hour;

online demo link : consecutive number query demo - Stack Exchange Data Explorer

以上方式算是取巧,但优点是通用性好,不需要新增任何表格直接就能使用。

另外思考:假如2048数量不够用呢?

那何不换位思考,尝试cross join呢,这样就有2048*2048数量能使用了,但这时就需要用到rownumber函数了

select top 10000 row_number() over (order by t1.number) as number from master..spt_values T1,master..spt_values T2 where T1.type = 'P'

最后建议

以上方式终究还是有点效率不完美,假如真的遇到大量数据需要连号 + 多列情况,麻烦建立一个实体Table + PK

帮助我

假如平常我的回答或是文章有帮助到你,可以帮我的Linkedin加个联络人并点击认同技能
Wei | LinkedIn
或是Github点个追蹤或是Star,谢谢你 ^_^
Wei | Github