【解決方法】SQL Server 2008 で複数の WITH 句を結合するにはどうすればよいですか?

メンバーの皆さん、こんにちは。

たとえば、以下のような WITH 句ステートメントが 2 つ以上あります。
複数の WITH 句ステートメントを結合するにはどうすればよいですか? ありがとう

SQL

--WITH clause 1

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

--WITH clause 2

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS

(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

--WITH clause 3

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS

(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

解決策 1
解決策 2

解決策 1

私の知る限り、2 つの別々の CTE で UNION を使用することはできません。
いくつかのオプションがあります:
– 単一の CTE 内にクエリを配置し、その 1 つの CTE 内でユニオンを使用します
– CTE を使用せず、「従来の」SELECT ステートメントを使用します。
– 単一の CTE のビューを作成し、それらを結合します

例
オールイン

SQL

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    UNION ALL
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL

)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

CTEなし

SQL

SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
UNION ALL
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL

意見

SQL

CREATE VIEW Sales1 AS
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
 
CREATE VIEW Sales2 AS
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
 
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID

SELECT SalesPersonID,TotalSales, SalesYear FROM Sales1
UNION 
SELECT SalesPersonID,TotalSales, SalesYear FROM Sales2