派生表的范围

Question

我有一个相当复杂的 SQL 查询，我们需要返回许多列，每列代表表中的不同行。所有派生表都需要按值进行过滤，以仅返回该帐户的派生表。以下效果很好：

SELECT CurrentBalance.Value,
       CurrentBalance.Customer,
       Debt30Balance.Value    AS Expr1,
       Debt30Balance.Customer AS Expr2,
       Debt60Balance.Value    AS Expr3,
       Debt60Balance.Customer AS Expr4,
       Debt90Balance.Value    AS Expr5,
       Debt90Balance.Customer AS Expr6,
       WIPCurrent.Value       AS Expr7,
       WIPCurrent.Customer    AS Expr8,
       WIP30Days.Value        AS Expr9,
       WIP30Days.Customer     AS Expr10,
       WIP60Days.Value        AS Expr11,
       WIP60Days.Customer     AS Expr12,
       WIP90Days.Value        AS Expr13,
       WIP90Days.Customer     AS Expr14
FROM   (SELECT TOP (1) Value,
                       Customer
        FROM   DebtBreakdown
        WHERE  ( Customer = @CustomerID )
               AND ( Type = 0 )
        ORDER  BY Timestamp DESC) AS CurrentBalance
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_7
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 1 )
                   ORDER  BY Timestamp DESC) AS Debt30Balance
         ON CurrentBalance.Customer = Debt30Balance.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_6
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 2 )
                   ORDER  BY Timestamp DESC) AS Debt60Balance
         ON Debt30Balance.Customer = Debt60Balance.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_5
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 3 )
                   ORDER  BY Timestamp DESC) AS Debt90Balance
         ON Debt60Balance.Customer = Debt90Balance.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_4
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 4 )
                   ORDER  BY Timestamp DESC) AS WIPCurrent
         ON Debt90Balance.Customer = WIPCurrent.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_3
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 5 )
                   ORDER  BY Timestamp DESC) AS WIP30Days
         ON WIPCurrent.Customer = WIP30Days.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_2
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 6 )
                   ORDER  BY Timestamp DESC) AS WIP60Days
         ON WIP30Days.Customer = WIP60Days.Customer
       INNER JOIN (SELECT TOP (1) Value,
                                  Customer
                   FROM   DebtBreakdown AS DebtBreakdown_1
                   WHERE  ( Customer = @CustomerID )
                          AND ( Type = 7 )
                   ORDER  BY Timestamp DESC) AS WIP90Days
         ON WIP60Days.Customer = WIP90Days.Customer  

但是，我需要能够过滤给定参数以外的其他内容。基本上，我想要做的是选择客户记录，给定参数（例如客户名称），选择 ID，然后将其用于派生表。我尝试过连接，但派生表不在任何连接的范围内。

有什么想法吗？

Answer 1

如果您至少使用 SQL Server 2005（从括号内的 TOP 表达式看来很可能如此），您可以使用公共表表达式而不是派生表。

/*Any previous statement must be terminated with a semi colon*/
WITH CurrentBalance
     AS (SELECT TOP (1) Value,
                        Customer
         FROM   DebtBreakdown
         WHERE  ( Customer = @CustomerID )
                AND ( Type = 0 )
         ORDER  BY Timestamp DESC), /*Comma delimit CTE definitions*/
     Debt30Balance
     AS (SELECT TOP (1) Value,
                        Customer
         FROM   DebtBreakdown AS DebtBreakdown_7
         WHERE  ( Customer = @CustomerID )
                AND ( Type = 1 )
         ORDER  BY Timestamp DESC)
        /* ETC ETC */

您可以在同一查询中多次引用 CTE。