相当于Oracle CONNECT BY和LEVEL伪列的SQL Server [英] SQL Server equivalent to Oracle CONNECT BY and LEVEL pseudocolumn
问题描述
请帮助将Oracle查询转换为等效的SQL Server:
Please help converting Oracle query to SQL Server equivalent:
SELECT (LEVEL+1-1) AS lvl
FROM dual
CONNECT BY LEVEL <= 10
/
输出的数字是1到10:
The output is numbers from 1 to 10:
LVL
----
1
2
3
...
10
我知道SQL Server中有层次结构方法以及诸如GetLevel
之类的内置函数.可以用来获得相同的结果吗?
I know there is hierarchy methods in SQL Server and built-ins like GetLevel
and more. Can this be used to get the same results?
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/
CREATE TABLE DUAL
(
DUMMY VARCHAR(1)
)
GO
INSERT INTO DUAL (DUMMY)
VALUES ('X')
GO
专门寻找可以使用smth的示例.就像查询中的LEVEL.例如:表格中只有一个开始日期-4/22/2013.但是使用LEVEL,我可以按如下所示进行递增:
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:
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
非常感谢大家.
推荐答案
我过去做过的一种方法是像这样查询spt_values
:
One way I've done it in the past is querying spt_values
like this:
SELECT number
FROM master..spt_values
WHERE
type = 'P'
AND number <= 255
但是,它没有完整的数字列表.另一种选择是创建像这样的递归CTE:
However, it doesn't have a full list of numbers. An alternative option would be to create a Recursive CTE like such:
WITH CTE AS (
SELECT 1 as Number
UNION ALL
SELECT Number+1
FROM CTE
WHERE Number < 100
)
SELECT * FROM CTE
这篇关于相当于Oracle CONNECT BY和LEVEL伪列的SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!