是否可以为公用表表达式创建嵌套的WITH子句？
是否可以为公用表表达式创建嵌套的WITH子句？
提问于 2009-09-11 22:07:36
WITH y AS (
WITH x AS (
SELECT * FROM MyTable
)
SELECT * FROM x
)
SELECT * FROM y像这样的东西能工作吗？我早些时候试过了，但我不能让它工作。
回答 6
Stack Overflow用户
回答已采纳
发布于 2009-09-11 22:12:01
虽然不是严格嵌套，但您可以使用公用表表达式在后续查询中重用以前的查询。
要执行此操作，您要查找的语句的格式为
WITH x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM yStack Overflow用户
发布于 2009-09-11 22:12:48
您可以执行以下操作，这称为递归查询：
WITH y
AS
(
SELECT x, y, z
FROM MyTable
WHERE [base_condition]
UNION ALL
SELECT x, y, z
FROM MyTable M
INNER JOIN y ON M.[some_other_condition] = y.[some_other_condition]
)
SELECT *
FROM y您可能不需要此功能。为了更好地组织我的查询，我做了以下工作：
WITH y
AS
(
SELECT *
FROM MyTable
WHERE [base_condition]
),
x
AS
(
SELECT *
FROM y
WHERE [something_else]
)
SELECT *
FROM xStack Overflow用户
发布于 2016-03-25 03:44:27
这些答案都很好，但就正确订购商品而言，您最好阅读本文http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge
以下是他的查询的一个示例。
WITH paths AS (
SELECT
EmployeeID,
CONVERT(VARCHAR(900), CONCAT('.', EmployeeID, '.')) AS FullPath
FROM EmployeeHierarchyWide
WHERE ManagerID IS NULL
UNION ALL
SELECT
ehw.EmployeeID,
CONVERT(VARCHAR(900), CONCAT(p.FullPath, ehw.EmployeeID, '.')) AS FullPath
FROM paths AS p
JOIN EmployeeHierarchyWide AS ehw ON ehw.ManagerID = p.EmployeeID
)
SELECT * FROM paths order by FullPath页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1413516
复制相关文章
相似问题