如何将 sql 中的两个 cte 语句与声明合并 错误：关键字 'DECLARE' 附近的语法不正确

Question

我在sql中使用CTE联合两列。如果我正在执行这两个查询，它工作得很好。但我想对这两个进行并并以得到更简单的O/p

我得到编译时错误:

关键字'DECLARE'附近语法错误。

谁能告诉我如何实现与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     

Answer 1

从哪里开始。

• 不能在union语句中使用declare语句。
• Union语句必须返回相同的列数。
• Common table expressions应该只定义在语句的开头。
• 您的Sum聚合需要每个月返回一个group by。

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

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

Answer 2

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

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