Область применения производных таблиц

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  

Но мне нужно иметь возможность фильтровать что-то, кроме указанного параметра. По сути, я хочу выбрать запись о клиенте, учитывая такой параметр, как имя клиента, выбрать идентификатор и использовать его для производных таблиц. Я пытался с Join, но производные таблицы не входят в сферу каких-либо объединений.

Есть идеи?

Answer 1

Если вы работаете хотя бы на SQL Server 2005 (как кажется, в скобках TOP выражение) вы можете использовать Common Table Expressions вместо производных таблиц.

/*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 несколько раз в одном запросе.