如何在sql server 2008中联合多个WITH子句？ [英] How do I union multiple WITH clause in sql server 2008?

问题描述

嗨成员，



例如我有两个或更多的WITH子句声明如下：

我如何将多个WITH子句声明组合在一起？谢谢

Hi members,

For example I have 2 or more WITH clause statement something like below:
How do I union multiple WITH clause statements together? thanks

--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 

推荐答案

据我所知你不能使用UNION在两个单独的CTE上。

你有一些选择：

- 在一个CTE中放置查询并在一个CTE中使用union

- 不要使用CTE但是'传统'SELECT语句

- 为单个CTE创建一个视图并将它们组合



例子

全部-in

As far as I know you can't use UNION on two separate CTE's.
Some options you have:
- Place queries inside a single CTE and use union inside the one CTE
- Don't use CTE but 'traditional' SELECT statements
- Create a view for a single CTE and combine them

Examples
All-in

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

No CTE

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

< br $>
查看

View

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

这篇关于如何在sql server 2008中联合多个WITH子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！