UNIONALL與CTE

我正在嘗試聯合所有兩個表 1）當前收據 2）歷史收據。這兩個表都有 CTE，因為這是將數字欄位更改為日期欄位的唯一方法。

這是我的當前收據表

;WITH t AS 
(SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1 THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNT] r)
SELECT [Day of Week] = ProperDate, [Saturday] = DATEADD
       (DAY, 6 - ((DATEPART(WEEKDAY, ProperDate)   @@DATEFIRST - 1) % 7), ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK,ws.WHDNAM,wv.VNVEN#,wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
  WHERE ws.WHAFLG = 'Y'
  GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
  ORDER BY  wm.ITPPCK DESC

這是我的歷史收據

;WITH t AS 
(
  SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1
    THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNH] r
)
SELECT [Day of Week] = ProperDate,[Saturday] = DATEADD (DAY,  6 - ((DATEPART(WEEKDAY, ProperDate)   @@DATEFIRST - 1) % 7),  ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
WHERE  ws.WHAFLG = 'Y' and ProperDate BETWEEN @Last2WeekDATE AND @LWDATE
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN;

所有列名都相同。我認為我只需要將 UNION ALL 放在兩者之間，然后將 GROUP BY 和 ORDER BY 放在最后，它就會起作用。但是，如果我將兩個 CTE 都保留為正確的日期，它基本上會給我一個錯誤“在 UNION ALL 之后的第二個 CTE 行附近的語法不正確”。

我知道它看起來像很多文本，但它是兩個表之間完全相同的欄位和相同的連接，這兩個表之間的唯一區別是 1 個來自表 [RECTRNT] - 最近的收據和第二個是來自 [RECTRNH] - 歷史收據。

其他一切都是一樣的。

我會欣賞任何想法。

uj5u.com熱心網友回復：

;WITH Current_data_cte AS 
(SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1 THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNT] r)
  ,Current_data as(
SELECT [Day of Week] = ProperDate, [Saturday] = DATEADD
       (DAY, 6 - ((DATEPART(WEEKDAY, ProperDate)   @@DATEFIRST - 1) % 7), ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK,ws.WHDNAM,wv.VNVEN#,wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
  WHERE ws.WHAFLG = 'Y'
  GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
  )--ORDER BY  wm.ITPPCK DESC
  
 , Historical_data_cte AS 
(
  SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), r.RCLDTE)) = 1
    THEN CONVERT(date, CONVERT(char(8), r.RCLDTE)) END
  FROM [Repit].[LEVYDTA].[RECTRNH] r
), 
Historical_data as(
SELECT [Day of Week] = ProperDate,[Saturday] = DATEADD (DAY,  6 - ((DATEPART(WEEKDAY, ProperDate)   @@DATEFIRST - 1) % 7),  ProperDate)
          ,CASE WHEN wm.[ITPPCK] = 'B'  THEN wp.[PPCQTY] * SUM(t.[RCRQTY]) ELSE 0 END AS 'PREPACKQTY',wp.[PPCQTY],SUM(t.RCRQTY) AS 'RCRQTY'
          ,t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITPPCK, wm.ITCSPK ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN
FROM t
LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSITMM] wm
  ON t.[RCITM#]=wm.[ITITM#] 
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSVENM] wv
  ON wm.[ITVEN#]=wv.[VNVEN#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSWHSM] ws
  ON t.[RCWHS#]=ws.[WHWHS#]
  LEFT OUTER JOIN [Repit].[LEVYDTA].[WHSPPKM] wp
  ON t.[RCITM#]=wp.[PPPPK#]
WHERE  ws.WHAFLG = 'Y' and ProperDate BETWEEN @Last2WeekDATE AND @LWDATE
GROUP BY t.ProperDate, wm.ITPPCK, wp.PPCQTY, t.RCRQTY, t.RCLDTE, t.RCWHS#, t.RCITM#, wm.ITCSPK
  ,ws.WHDNAM, wv.VNVEN#, wv.VNVENN;
  )
  select * from Current_data
  union
  select * from Historical_data

標籤：

上一篇：在SQLServer中透視員工表？

下一篇：如果N:N表上存在值，則繼續獲取所有記錄