如何将sql中的两个cte语句与declare ERROR结合:关键字“declare”附近的不正确语法

I am using CTE for union two columns in sql. If I am executing these two queries it is working fine . But I want to make union of these two for more simpler O/P.

我在sql中使用CTE进行联合两列。如果我正在执行这两个查询，它可以正常工作。但是我想把这两个结合起来使O/P更简单。

I am getting compile time error :

我得到编译时间错误:

Incorrect syntax near the keyword 'DECLARE'.

在关键字“DECLARE”附近的语法错误。

can anyone tell me hoe to achieve union with cte

谁能告诉我怎样与cte结合

DECLARE @Inward DATETIME
SET @Inward = DATEADD(mm, -6, CURRENT_TIMESTAMP);
WITH cte AS 
(
SELECT 0 AS TheMonth 
UNION ALL 
SELECT TheMonth + 1 
FROM cte
WHERE TheMonth < 5
) 
SELECT  
cte.TheMonth,
isnull(sum(qty),0) as inward 
FROM 
cte 
LEFT OUTER JOIN RS_GIN_Master as g 
ON accept_date >= DATEADD(MM, cte.TheMonth, @Inward) AND accept_date < DATEADD(MM, cte.TheMonth + 1, @Inward)

UNION all 

DECLARE @Outward DATETIME
SET @Outward = DATEADD(mm, -6, CURRENT_TIMESTAMP);
WITH cte AS
(
SELECT 0 AS TheMonthO 
UNION ALL
SELECT TheMonthO + 1 
FROM cte 
 WHERE TheMonthO < 5
) 

SELECT  isnull(sum(quantity),0) as outward 
FROM 
cte 
LEFT OUTER JOIN 
RS_Sell_Order_Master as s 
ON del_date >= DATEADD(MM, cte.TheMonthO, @Outward) AND del_date < DATEADD(MM, cte.TheMonthO + 1, @Outward) and isDelivered = 1
left outer join RS_Sell_Order_Mapping as sm on sm.sell_order_no = s.sell_order_no

2 个解决方案

#1

Where to begin.

从哪里开始。

You cannot use declare statements inside of union statements.
不能在union语句中使用declare语句。
Union statements must return the same number of columns.
Union语句必须返回相同数量的列。
Common table expressions should only be defined at the beginning of your statement.
公共表表达式应该只在语句开始时定义。
Your Sum aggregate needs a group by to return each month.
你的总和每月需要一组回来。

Perhaps you're looking for something like this:

也许你正在寻找这样的东西:

DECLARE @Inward DATETIME
DECLARE @Outward DATETIME

SET @Inward = DATEADD(mm, -6, CURRENT_TIMESTAMP);
SET @Outward = DATEADD(mm, -6, CURRENT_TIMESTAMP);

WITH cte AS 
(
   SELECT 0 AS TheMonth 
   UNION ALL 
   SELECT TheMonth + 1 
   FROM cte
   WHERE TheMonth < 5
) 
SELECT  
   cte.TheMonth,
   isnull(sum(qty),0) as inward,
   null as outward
FROM 
   cte 
      LEFT OUTER JOIN RS_GIN_Master as g 
          ON accept_date >= DATEADD(MM, cte.TheMonth, @Inward) 
         AND accept_date < DATEADD(MM, cte.TheMonth + 1, @Inward)
GROUP BY cte.TheMonth
UNION all 
SELECT  
   cte.TheMonth,
   null as inward,
   isnull(sum(quantity),0) as outward 
FROM 
   cte 
      LEFT OUTER JOIN RS_Sell_Order_Master as s 
          ON del_date >= DATEADD(MM, cte.TheMonthO, @Outward) 
         AND del_date < DATEADD(MM, cte.TheMonthO + 1, @Outward) and isDelivered = 1
      left outer join RS_Sell_Order_Mapping as sm on 
          sm.sell_order_no = s.sell_order_no     
GROUP BY cte.TheMonth

#2

Ha! I'm looked it today because i had the same question. You must declare the first CTE with the "WITH" Keyword and every CTE you need, just separate it with a comma.

哈!我今天看到了，因为我有同样的问题。您必须使用“with”关键字和需要的每个CTE声明第一个CTE，只需用逗号分隔它。

EDIT from sgeedes Query above

从上面的sgeedes查询进行编辑

    DECLARE @Inward DATETIME
DECLARE @Outward DATETIME

SET @Inward = DATEADD(mm, -6, CURRENT_TIMESTAMP);
SET @Outward = DATEADD(mm, -6, CURRENT_TIMESTAMP);

WITH cte AS 
(
   SELECT 0 AS TheMonth 
   UNION ALL 
   SELECT TheMonth + 1 
   FROM cte
   WHERE TheMonth < 5
) 
SELECT TheMonth,sum(Inward) as InWard, sum(OutWard) as OutWard
FROM
(
SELECT  
   cte.TheMonth,
   isnull(sum(qty),0) as inward,
   0 as outward
FROM 
   cte 
      LEFT OUTER JOIN RS_GIN_Master as g 
          ON accept_date >= DATEADD(MM, cte.TheMonth, @Inward) 
         AND accept_date < DATEADD(MM, cte.TheMonth + 1, @Inward)
GROUP BY cte.TheMonth
UNION all 
SELECT  
   cte.TheMonth,
   0 as inward,
   isnull(sum(quantity),0) as outward 
FROM 
   cte 
      LEFT OUTER JOIN RS_Sell_Order_Master as s 
          ON del_date >= DATEADD(MM, cte.TheMonthO, @Outward) 
         AND del_date < DATEADD(MM, cte.TheMonthO + 1, @Outward) and isDelivered = 1
      left outer join RS_Sell_Order_Mapping as sm on 
          sm.sell_order_no = s.sell_order_no     
GROUP BY cte.TheMonth
)Z

#1