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

小编典典

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

sql

更清楚地说，我有这类数据。

查询1）2016年数据

Item       Price        Quantity

Shoe        20             10
Shoe        30             15
Cups        10             30
Towels      30             20
Towels      25             20
Towels      20             20

查询2）2017年的数据

Item       Price        Quantity

Shoe        40             30
Shoe        50             20
Towels      30             30
Towels      20             30

查询3）2018年的数据

Item       Price        Quantity

Shoe        20             10
Cups        10             30
Towels      30             20
Towels      25             20
Towels      20             20

查询1）2019年的数据

Item       Price        Quantity

Shoe        20             10
Shoe        30             15
Cups        10             30
Towels      30             20
Towels      25             20
Towels      20             20

我想要这样的结果：

Item   Price2016   Quantity2016   Price2017   Quantity2017  Price2018   Quantity2018   Price2019   Quantity2019

Shoe      20           10            40            30          20          10             20            10
Shoe      30           15            50            20                                     30            15

我尝试使用Joins，Unions甚至创建Temp Tables或cursor

例如，inner Join产生以下结果：

Item       Price2016        Quantity 2016       Price2017        Quantity 2017 ...

Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10

请注意，本例中的数据是不准确的，但结果与此类似。

任何想法我如何使用获得我的首选结果 SQL

编辑：我从中获取数据的查询是

select Item, Price, sum(quantity) as quantity from Sales where year(itemsold) = 2016 group by Item, price

我只是更改年份以获取其他数据。

阅读 156

2021-04-07

共1个答案

小编典典

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

演示

十进制表和插入记录

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

2021-04-07