以调试友好的方式重写/重新格式化多个嵌套子查询

Question

有没有更好的方法来重写多个嵌套的子查询，以便更容易理解和调试，而不会降低性能？

(注意：在我的情况下，我得到了一堆相当复杂的子查询，许多子查询具有多个连接，其中子句和一些子句得到了案例语句等。 将所有内容编写为一个大块代码是调试的噩梦(

例如：我想重写这个：

SELECT DoSomething(x) AS x
FROM (SELECT DoSomething(x) AS x
      FROM (SELECT DoSomething(x) AS x
            FROM (SELECT DoSomething(x) AS x
                  FROM (SELECT DoSomething(x) AS x
                        FROM (SELECT x
                              FROM tbl_1) AS Q1) AS Q2) AS Q3) AS Q4) AS Q5

变成这样的东西：

--Q1
SELECT *
INTO #Q1
FROM (SELECT x
      FROM tbl_1) AS Q1
--Q2
SELECT *
INTO #Q2
FROM (SELECT DoSomething(x)
      FROM Q1) AS Q2
--Q3
SELECT *
INTO #Q3
FROM (SELECT DoSomething(x)
      FROM Q2) AS Q3
--Q4
SELECT *
INTO #Q4
FROM (SELECT DoSomething(x)
      FROM Q3) AS Q3
--Q5
SELECT *
INTO #Q5
FROM (SELECT DoSomething(x)
      FROM Q4) AS Q4
--Do Something with Q5 then cleanup
DROP TABLE #Q1
DROP TABLE #Q2
DROP TABLE #Q3
DROP TABLE #Q4
DROP TABLE #Q5

上面的例子虽然有更多的代码，但(希望(允许调试器更轻松地查看/选择单个子查询，并帮助他/她缩小潜在问题的范围

Answer 1

您可能想看看 CTE。

您可以像这样编写示例：

WITH
Q1
AS
(
SELECT x
       FROM tbl_1
),
Q2
AS
SELECT DoSomething(x)
       FROM Q1
),
Q3
AS
(
SELECT DoSomething(x)
       FROM Q2
),
Q4
AS
(
SELECT DoSomething(x)
       FROM Q3
),
Q5
AS
(
SELECT DoSomething(x)
       FROM Q4
)
SELECT *
       FROM Q5;