当 4 个查询的数据不唯一时，如何将 4 个查询的数据插入到一个表中? [英] How to insert data from 4 queries into one table when the 4 queries have data that are not unique?
问题描述
更清楚地说，我有这些数据.
to be more clear I have these kind of data.
查询 1) 2016 年的数据
Query 1) Data from 2016
Item Price Quantity
Shoe 20 10
Shoe 30 15
Cups 10 30
Towels 30 20
Towels 25 20
Towels 20 20
查询 2) 2017 年的数据
Query 2) Data from 2017
Item Price Quantity
Shoe 40 30
Shoe 50 20
Towels 30 30
Towels 20 30
查询 3) 2018 年数据
Query 3) Data from 2018
Item Price Quantity
Shoe 20 10
Cups 10 30
Towels 30 20
Towels 25 20
Towels 20 20
查询 1) 2019 年的数据
Query 1) Data from 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
I tried using Joins, Unions, even creating Temp Tables or a cursor
例如，inner Join 产生这样的结果:
for example, inner Join produce this result:
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
请注意，此示例中的数据不准确，但结果与此类似.
Please do take note that the data in this example is innacurate but the result is similar to this.
知道如何使用 SQL
我从中获取数据的查询是这个
The query that I get the data from is this
select Item, Price, sum(quantity) as quantity from Sales where year(itemsold) = 2016 group by Item, price
我只是更改年份以获取其他数据.
I just change the year to get the other data.
推荐答案
J,
我看过上面的解决方案，它也是有效的，但您也可以尝试使用 PIVOT.我已经为您创建了一个演示，请查看此解决方案也可能对您有所帮助.
I have seen the above solution and it is also valid but you also can try using PIVOT. I have created a demo for you, please check this solution also that might helps you.
演示
DECLARE TABLES &插入记录
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
这篇关于当 4 个查询的数据不唯一时，如何将 4 个查询的数据插入到一个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
