把两个表合并在一起，加上数量

I have 2 tables

我有两个表

warehouse_1

item  qty 
--------
3001   1
3003   3
3004   1
3006   2

warehouse_2

item   qty
----------
3001    2
3002    3
3006    3
3008    6

I want to union these two tables into a third and add the quantities of the same items like this

我想把这两个表合并到第三个表中，然后像这样添加相同项目的数量

warehouse_3

item    qty
-----------
3001     3
3002     3
3003     3
3004     1
3006     5
3008     6

How can I achieve this with SQL Server?

如何使用SQL Server实现这一点?

5 个解决方案

#1

You can achieve it using UNION. First you have to union all data (of both table) and using sub-query you can group using GROUP BY and add total using SUM function.

您可以使用UNION实现它。首先，您必须联合所有数据(两个表的数据)，并使用子查询，您可以使用group BY进行分组，并使用SUM函数添加total。

SELECT Item, SUM(qty) as Qty FROM
(
   SELECT Item, qty FROM warehouse_1
   UNION
   SELECT Item, qty FROM warehouse_2
) as a
GROUP BY item;

See more about UNION:

看到更多关于联盟:

SQL Server: UNION (Transact-SQL)
SQL Server:联盟(transact - SQL)
MySQL: UNION Syntax
MySQL:联盟语法

See more about GROUP BY clause: (You can also find about SUM there.)

查看更多关于GROUP BY子句:(您也可以在那里找到SUM。)

SQL Server: GROUP BY (Transact-SQL)
SQL Server:按组(Transact-SQL)
MySQL: GROUP BY (Aggregate) Functions
MySQL:按(聚合)函数分组

#2

CREATE OR REPLACE VIEW warehouse_3 as 
    SELECT coalesce(w1.item,w2.item) as item, w1.qty+w2.qty as qty
    FROM warehouse_1 as w1
    OUTER JOIN warehouse_2 as w2
    ON w1.item = w2.item;

Assuming that item is unique. coalesce is nvl in some databases. Then you can just call:

假设该项目是唯一的。在一些数据库中，coalesce是nvl。然后你可以打:

select * from warehouse_3;

and it will return the sum.

它会返回和。

Also if you change some of the quantities in W1 or W2 it will be automatically corrected in W3

另外，如果你改变W1或W2中的一些量，它将在W3中自动修正

#3

You can try this

你可以试试这个

SELECT item , SUM(qty)
FROM (
   SELECT item, qty
   FROM warehouse_1
   UNION
   SELECT item, qty
   FROM warehouse_2
) AS U GROUP BY item

#4

try this:

试试这个:

create table tbl1(item int,qty int)
INSERT INTO tbl1
VALUES(3001,1),(3003,3),(3004,1),(3006,2)

create table tbl2(item int,qty int)
INSERT INTO tbl2
VALUES(3001,2),(3002,3),(3006,3),(3008,6)


select isnull(t1.item,t2.item) as item,isnull(t1.qty,0)+isnull(t2.qty,0) as qty from tbl1 t1 full outer join tbl2 t2 on t1.item =t2.item 
order by 1

#5

Please try:

请尝试:

select item,sum(qty) from(
    select item, Qty from warehouse_1 
    UNION
    select item, Qty from warehouse_2
)x
group by item

#1