时间戳

首页 > 解决方案 > SQL Server：UNION，包含数据的列，删除没有的列

sql-server - SQL Server：UNION，包含数据的列，删除没有的列

问题描述

我有两张桌子，LYEAR并且CYEAR：

[SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

列[SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8]填充有来自表格CYEAR和表格的信息。 [SKU],[Title],[9],[10],[11],[12]LYEAR

我将如何合并这两个表，以便删除具有 NULL 值的列，让每个 SKU 留有一行？

我已经做到了这一点：

WITH LYEAR_ORG ([SKU],[Title],[QTY],[DATE]) AS 
(
    SELECT 
        T1.ItemNumber, T1.ItemTitle, (SUM(T2.nqty)) AS [UNITS], 
        CONVERT(VARCHAR(2), (MONTH(T3.dProcessedOn)))
    FROM 
        StockItem T1
    LEFT OUTER JOIN 
        OrderItem T2 ON T1.pkstockItemId = T2.fkStockItemID_processed
    LEFT JOIN 
        [Order] T3 ON T3.pkOrderID = T2.fkOrderID
    WHERE   
        (YEAR(T3.dProcessedOn)) = (YEAR(getdate())-1) 
        AND (MONTH(T3.dProcessedOn)) > (MONTH(getdate())-1)
    GROUP BY 
        T1.ItemNumber, T1.ItemTitle, 
        CONVERT(VARCHAR(2), (MONTH(T3.dProcessedOn)))
),
CYEAR_ORG ([SKU],[Title],[QTY],[DATE]) AS 
(
    SELECT T1.ItemNumber, T1.ItemTitle, (SUM(T2.nqty)) AS [UNITS], CONVERT(varchar(2),(MONTH(T3.dProcessedOn)))
    FROM StockItem T1
    LEFT OUTER JOIN OrderItem T2 ON T1.pkstockItemId = T2.fkStockItemID_processed
    LEFT JOIN [Order] T3 ON T3.pkOrderID = T2.fkOrderID
    WHERE   (YEAR(T3.dProcessedOn)) = (YEAR(getdate())-1) 
            AND (MONTH(T3.dProcessedOn)) < (MONTH(getdate()))
    GROUP BY T1.ItemNumber, T1.ItemTitle, CONVERT(varchar(2),(MONTH(T3.dProcessedOn)))
    ),

LYEAR ([SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) AS (
    SELECT *
    FROM LYEAR_ORG
    PIVOT ( MAX(QTY) FOR DATE in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as LYEAR_ORDERS
    ),

CYEAR ([SKU],[Title],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) AS (
    SELECT *
    FROM CYEAR_ORG
    PIVOT ( MAX(QTY) FOR DATE in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as CYEAR_ORDERS
    )

SELECT * FROM LYEAR
UNION ALL
SELECT * FROM CYEAR

我无法使用INSERT INTO，因为这是我正在使用的 Linnworks 平台的限制，它是 SQL Server。

标签： sql-servertsqlunion

解决方案

假设 LYEAR 列 1-8 全部为 NULL，CYEAR 9-12 也是如此，JOIN带有大量COALESCEs 的 a 而不是 aUNION会让你到达你想去的地方。但是，如果任何列中都有这两年的值，那么这将是横向的。

SELECT
  l.[SKU]
 ,l.[Title]
 ,COALESCE(l.[1], c.[1]) AS [1]
 ,COALESCE(l.[2], c.[2]) AS [2]
 ,COALESCE(l.[3], c.[3]) AS [3]
 ,COALESCE(l.[4], c.[4]) AS [4]
 ,COALESCE(l.[5], c.[5]) AS [5]
 ,COALESCE(l.[6], c.[6]) AS [6]
 ,COALESCE(l.[7], c.[7]) AS [7]
 ,COALESCE(l.[8], c.[8]) AS [8]
 ,COALESCE(l.[9], c.[9]) AS [9]
 ,COALESCE(l.[10], c.[10]) AS [10]
 ,COALESCE(l.[11], c.[11]) AS [11]
 ,COALESCE(l.[12], c.[12]) AS [12]
FROM
  LYEAR AS l
  JOIN
    CYEAR AS c
      ON
      c.SKU = l.SKU
      AND c.Title = l.Title;

推荐阅读