如何在sql server 2008中联合多个WITH子句？

本文介绍了如何在sql server 2008中联合多个WITH子句？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 嗨成员， 例如我有两个或更多的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 1WITH 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, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonID--WITH clause 2WITH 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, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonID--WITH clause 3WITH 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, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonID 推荐答案 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, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonID 否CTE No CTE SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYearFROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULLUNION ALLSELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYearFROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULL 查看 ViewCREATE VIEW Sales1 ASWITH 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, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonID CREATE VIEW Sales2 ASWITH 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, SalesYearFROM Sales_CTEGROUP BY SalesYear, SalesPersonIDSELECT SalesPersonID,TotalSales, SalesYear FROM Sales1UNION SELECT SalesPersonID,TotalSales, SalesYear FROM Sales2 这篇关于如何在sql server 2008中联合多个WITH子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！