SQL Server 相当于 Oracle CONNECT BY 和 LEVEL 伪列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15031897/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server equivalent to Oracle CONNECT BY and LEVEL pseudocolumn
提问by Art
Please help converting Oracle query to SQL Server equivalent:
请帮助将 Oracle 查询转换为等效的 SQL Server：
SELECT (LEVEL+1-1) AS lvl
FROM dual
CONNECT BY LEVEL <= 10
/
The output is numbers from 1 to 10:
输出是从 1 到 10 的数字：
LVL
----
1
2
3
...
10
I know there is hierarchy methods in SQL Server and built-ins like GetLeveland more. Can this be used to get the same results?
我知道 SQL Server 中存在层次结构方法以及诸如此类的内置函数GetLevel。这可以用来获得相同的结果吗？
To create dual table if needed (not sure) - copied from here:http://blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/
如果需要（不确定）创建双表 - 从这里复制：http: //blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/
CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO
Specifically looking for examples that would let use smth. like LEVEL in queries. For example: there is only one start date in the table - 4/22/2013. But with LEVEL I'm able to increment it as follows:
专门寻找可以使用 smth 的示例。就像查询中的 LEVEL。例如：表中只有一个开始日期 - 4/22/2013。但是使用 LEVEL 我可以按如下方式增加它：
SELECT start_date, start_date+LEVEL-1 AS start_date_btwn
FROM my_tab
WHERE id = 1
CONNECT BY LEVEL<=10
/
START_DATE START_DATE_BTWN
------------------------------
4/22/2013 4/22/2013
4/22/2013 4/23/2013
4/22/2013 4/24/2013
4/22/2013 4/25/2013
......
4/22/2013 4/30/2013
Thank you very much to all in advance.
非常感谢大家提前。
回答by sgeddes
One way I've done it in the past is querying spt_valueslike this:
我过去做过的一种方法是这样查询spt_values：
SELECT number
FROM master..spt_values
WHERE
type = 'P'
AND number <= 255
However, it doesn't have a full list of numbers. An alternative option would be to create a Recursive CTE like such:
但是，它没有完整的数字列表。另一种选择是创建一个递归 CTE，如下所示：
WITH CTE AS (
SELECT 1 as Number
UNION ALL
SELECT Number+1
FROM CTE
WHERE Number < 100
)
SELECT * FROM CTE
