当4个查询的数据不唯一时，如何将4个查询的数据插入到一个表中？

当4个查询的数据不唯一时，如何将4个查询的数据插入到一个表中？

我已经看到了上面的解决方案，它也是有效的，但是您也可以尝试使用
。我已经为您创建了一个演示，请同时检查此解决方案，它可能会对您有所帮助。

演示

十进制表和插入记录

DECLARE @Table2016 AS TABLE ( Item VARCHAr(50), Price FLOAT, Quantity INT ); DECLARE @Table2017 AS TABLE ( Item VARCHAr(50), Price FLOAT, Quantity INT ); DECLARE @Table2018 AS TABLE ( Item VARCHAr(50), Price FLOAT, Quantity INT );DECLARE @Table2019 AS TABLE ( Item VARCHAr(50), Price FLOAT, Quantity INT );INSERT INTO @Table2016 (Item,Price,Quantity) VALUES('Shoe'  ,20,10),('Shoe'  ,30,15),('Cups' ,10,30),('Towels',30,20),('Towels',25,20),('Towels',20,20)INSERT INTO @Table2017 (Item,Price,Quantity) VALUES('Shoe'  ,40,30),('Shoe'  ,50,20),('Towels',30,30),('Towels',20,30)INSERT INTO @Table2018 (Item,Price,Quantity) VALUES('Shoe'  ,20,10),('Cups'  ,10,30),('Towels',30,20),('Towels',25,20),('Towels',20,20)INSERT INTO @Table2019 (Item,Price,Quantity) VALUES('Shoe'  ,20,10),('Shoe'  ,30,15),('Cups'  ,10,30),('Towels',30,20),('Towels',25,20),('Towels',20,20)

标记所有表并插入到温度表中

    SELECt Item,Price,Quantity,PriceYear,QuantityYear INTO TempFinal    FROM (    SELECt Item,Price,Quantity, 'Price2016' as PriceYear,'Quantity2016' as QuantityYear FROM @Table2016    UNIOn ALL         SELECt Item,Price,Quantity, 'Price2017' as PriceYear,'Quantity2017' as QuantityYear FROM @Table2017    UNIOn ALL         SELECt Item,Price,Quantity, 'Price2018' as PriceYear,'Quantity2018' as QuantityYear FROM @Table2018    UNIOn ALL         SELECt Item,Price,Quantity, 'Price2019' as PriceYear,'Quantity2019' as QuantityYear FROM @Table2019    ) MyTables

没有GROUPBY的查询

    SELECt item, [Price2016],[Quantity2016],[Price2017],[Quantity2017],[Price2018],[Quantity2018],[Price2019],[Quantity2019]    FROM (    SELECt item,Price,Quantity,PriceYear,QuantityYear    FROM TempFinal) up    PIVOT (SUM(Quantity) FOR QuantityYear IN ([Quantity2016],[Quantity2017],[Quantity2018],[Quantity2019])) AS pvt    PIVOT (SUM(Price) FOR PriceYear IN ([Price2016],[Price2017],[Price2018],[Price2019])) AS pvt2    ORDER BY item

与GROUPBY查询

     SELECt item, SUM([Price2016])[Price2016],SUM([Quantity2016])[Quantity2016],SUM([Price2017])[Price2017],SUM([Quantity2017])[Quantity2017],SUM([Price2018])[Price2018],SUM([Quantity2018])[Quantity2018],SUM([Price2019])[Price2019],SUM([Quantity2019])[Quantity2019]    FROM (    SELECt item,Price,Quantity,PriceYear,QuantityYear    FROM TempFinal) up    PIVOT (SUM(Quantity) FOR QuantityYear IN ([Quantity2016],[Quantity2017],[Quantity2018],[Quantity2019])) AS pvt    PIVOT (SUM(Price) FOR PriceYear IN ([Price2016],[Price2017],[Price2018],[Price2019])) AS pvt2    GROUP by item    ORDER BY item

下降温度表

DROP TABLE TempFinal