with cte as (
select codefrom, codeto, town, codefrom as code
from t
union all
select codefrom, codeto, town, code + 1
from cte
where code < codeto
)
select *
from cte;
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3) --1,000 rows, Add more N for more rows
SELECT YT.CodeFrom,
YT.CodeTo,
YT.Town,
T.I AS Code
FROM (VALUES(1,7,'Paris'),
(14,17,'Sao Paulo'))YT(CodeFrom,CodeTo,Town)
JOIN Tally T ON YT.CodeFrom <= T.I
AND YT.CodeTo >= T.I;
2条答案
按热度按时间aurhwmvo1#
在sql server中,可以使用递归cte:
sql server的内置默认递归限制为100。所以,如果生成的代码可能超过100个,那么添加
option (maxrecursion 0)
.wnavrhmk2#
就像我在评论中戈登的回答中提到的,用一个数字来计算。到目前为止,它们的速度要快得多(尤其是对于较大的数据集),并且不会出现最大递归错误,因为它们不是递归的: