WITH y AS (
WITH x AS (
SELECT * FROM MyTable
)
SELECT * FROM x
)
SELECT * FROM y
像这样的东西能工作吗?我早些时候试过了,但我不能让它工作。
发布于 2009-09-11 22:12:01
虽然不是严格嵌套,但您可以使用公用表表达式在后续查询中重用以前的查询。
要执行此操作,您要查找的语句的格式为
WITH x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM y
发布于 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 x
发布于 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
https://stackoverflow.com/questions/1413516
复制相似问题