Блог

Итак, я попытался создать этот CTE с двумя производными таблицами. В моей первой производной таблице будут отображаться номера учетных записей, описания учетных записей для номеров учетных записей в диапазоне 500. Вторая производная таблица в CTE — должна содержать номера счетов, итоговые суммы счетов и идентификаторы счетов, общая сумма счетов-фактур которых превышает 1000.

Теперь я начал с создания этих двух таблиц отдельно, чтобы убедиться, что я знаю, что делаю. Они работали так:

 SELECT AccountNo, 
    AccountDescription
FROM Accounts
WHERE AccountNo BETWEEN 500 and 599

SELECT Accounts.AccountNo, 
    Invoices.InvoiceTotal, 
    Invoices.InvoiceID
FROM Accounts 
    JOIN InvoiceLineItems
        ON Accounts.AccountNo = InvoiceLineItems.AccountNo
    JOIN Invoices
        ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE Invoices.InvoiceTotal >= 1000
  

Теперь, когда я попытался превратить их в CTE, моя лучшая попытка выглядит так:

 WITH Accounts500to599 AS
(SELECT AccountNo, 
    AccountDescription
FROM Accounts
WHERE AccountNo BETWEEN 500 and 599
ORDER BY Accounts.AccountNo),

InvoicesAbove1000 AS 
(SELECT Accounts.AccountNo, 
    Invoices.InvoiceTotal, 
    Invoices.InvoiceID
FROM Accounts 
    JOIN InvoiceLineItems
        ON Accounts.AccountNo = InvoiceLineItems.AccountNo
    JOIN Invoices
        ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE Invoices.InvoiceTotal >= 1000
ORDER BY Accounts.AccountNo)
  

Теперь моя конечная цель — показать учетные записи со счетами-фактурами, превышающими среднюю сумму счета-фактуры, что должно быть достаточно просто, но мне еще многому предстоит научиться, когда дело доходит до создания этих CTE.

Любые советы будут оценены. Спасибо.

Обрабатывайте CTE так же, как и любую другую таблицу (или представление). Он создает виртуальную таблицу для использования.

Например, в приведенном выше примере вы можете сделать

 WITH Accounts500to599 AS
(SELECT AccountNo, 
    AccountDescription
FROM Accounts
WHERE AccountNo BETWEEN 500 and 599
-- ORDER BY Accounts.AccountNo -- Note - removed the order by
),

InvoicesAbove1000 AS 
(SELECT Accounts.AccountNo, 
    Invoices.InvoiceTotal, 
    Invoices.InvoiceID
FROM Accounts 
    JOIN InvoiceLineItems
        ON Accounts.AccountNo = InvoiceLineItems.AccountNo
    JOIN Invoices
        ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
WHERE Invoices.InvoiceTotal >= 1000
--ORDER BY Accounts.AccountNo   -- Note - removed the order by
)

SELECT A.AccountNo, A.AccountDescription, AVG(I.InvoiceTotal) AS AvgInvoiceTotal
FROM   Accounts500to599 A
       INNER JOIN InvoicesAbove1000 I ON A.AccountNo = I.AccountNo
GROUP BY A.AccountNo, A.AccountDescription
  

Это не отвечает на ваши «счета-фактуры больше среднего», поскольку есть некоторые вопросы о том, что это на самом деле означает, когда вы уже исключаете счета-фактуры <1000 долларов, но выше приведен пример того, как их можно использовать.

Обратите внимание, что порядок данных не имеет значения (как и в обычных таблицах), поэтому я прокомментировал предложения ORDER BY в CTE.

Традиционный CTE также функционально эквивалентен вложенному запросу в предложении FROM — поэтому приведенное выше аналогично

 SELECT A.AccountNo, A.AccountDescription, AVG(I.InvoiceTotal) AS AvgInvoiceTotal
FROM   (SELECT AccountNo, 
               AccountDescription
        FROM Accounts
        WHERE AccountNo BETWEEN 500 and 599
       ) A
       INNER JOIN 
      (SELECT Accounts.AccountNo, 
          Invoices.InvoiceTotal, 
         Invoices.InvoiceID
       FROM Accounts 
           JOIN InvoiceLineItems
                ON Accounts.AccountNo = InvoiceLineItems.AccountNo
           JOIN Invoices
                ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
      WHERE Invoices.InvoiceTotal >= 1000
    ) I ON A.AccountNo = I.AccountNo
GROUP BY A.AccountNo, A.AccountDescription
  

В качестве небольшого дополнения к ответу seanb, вот как вы можете попытаться увеличить свои счета-фактуры, превышающие среднее значение счетов-фактур. Взгляните на WHERE .

 ;WITH Accounts500to599 AS (
    SELECT
        AccountNo, 
        AccountDescription
    FROM AS Accounts
    WHERE
        AccountNo BETWEEN 500 and 599
),
InvoicesAbove1000 AS (
    SELECT
        Accounts.AccountNo, 
        Invoices.InvoiceTotal, 
        Invoices.InvoiceID
    FROM Accounts 
        JOIN InvoiceLineItems
            ON Accounts.AccountNo = InvoiceLineItems.AccountNo
        JOIN Invoices
            ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
    WHERE 
        Invoices.InvoiceTotal >= 1000
)
SELECT
    Accounts500to599.AccountNo,
    Accounts500to599.AccountDescription,
    InvoicesAbove1000.InvoiceID,
    InvoicesAbove1000.InvoiceTotal
FROM Accounts500to599
INNER JOIN InvoicesAbove1000
    ON Accounts500to599.AccountNo = InvoicesAbove1000.AccountNo
WHERE InvoicesAbove1000.InvoiceTotal > (
    
    SELECT 
        AVG( InvoiceTotal )
    FROM Accounts AS a
    INNER JOIN InvoiceLineItems l
        ON a.AccountNo = l.AccountNo
    INNER JOIN Invoices i
        ON l.InvoiceID = i.InvoiceID
    WHERE
        a.AccountNo BETWEEN 500 AND 599
        AND i.InvoiceTotal > 1000

)
ORDER BY
    Accounts500to599.AccountNo;