在SQL查询中根据给定范围对数字进行拆分

尝试这个：

;with Ranges as
    (
        select  1   as  NumberStart,
                10  as  NumberEnd
    ),
    InputSet as 
    (
        select  2   as  NumberStart,
                3   as  NumberEnd
        union all
        select  5,
                7
    ),
    cte as
    (
        select  (select min(NumberStart) from Ranges)   as  rn,
                (select max(NumberEnd) from Ranges)     as  rn_end
        union all
        select  rn+1,
                t.rn_end
        from    cte t
        where   t.rn < t.rn_end
    )
select  min(isnull(t2.NumberStart,t1.rn))                                   as  NumberStart,
        coalesce(t2.NumberEnd,v3.NumberEnd-1,v4.NumberEnd)                  as  NumberEnd,
        count(*)                                                            as  Cnt
from    cte t1
left join InputSet t2 on t1.rn between t2.NumberStart and t2.NumberEnd
outer apply (
                select  min(NumberStart)    as  NumberEnd
                from    InputSet tt
                where   tt.NumberStart > t1.rn
            ) v3
outer apply (
                select  max(NumberEnd)  as  NumberEnd
                from    Ranges
            ) v4
group by coalesce(t2.NumberEnd,v3.NumberEnd-1,v4.NumberEnd)
order by 1
option (maxrecursion 32000)

但！如果间隔设置不正确，它将不起作用：您必须确保间隔（InputSet）不相交，并且Start小于End。