有两个表A、B,结构是不一样的,但是其中有2列是完全一样的:
A表
Model Quantity ...
Model1 100 ...
Model2 50 ...
Model3 80 ...
...B表
Model Quantity ...
Model1 120 ...
Model3 180 ...
...我想返回如下结果,即A、B表相同Model的Quantity加起来
Model Quantity
Model1 220
Model2 50
Model3 260
...请问怎么写?好像要用Group by吧
A表
Model Quantity ...
Model1 100 ...
Model2 50 ...
Model3 80 ...
...B表
Model Quantity ...
Model1 120 ...
Model3 180 ...
...我想返回如下结果,即A、B表相同Model的Quantity加起来
Model Quantity
Model1 220
Model2 50
Model3 260
...请问怎么写?好像要用Group by吧
from A t1 full join B t2
on t1.Model=t2.Model
group by isnull(t1.Model,t2.Model)或者select Model, sum(Quantity)
from (select * from A union all select * from B)
group by Model
ISNULL(A.Model,B.Model) AS Model,
ISNULL(A.Quantity,0)+ISNULL(B.Quantity,0) A Quantity
FROM tbA AS A
FULL JOIN tbB AS B
ON A.Model=B.Model
A表
Model Quantity ...
Model1 100 ...
Model2 50 ...
Model3 80 ...
... B表
Model Shuliang ...
Model1 120 ...
Model3 180 ...
... 我想返回如下结果,即A、B表相同Model的Quantity加起来
Model Quan
Model1 220
Model2 50
Model3 260
... 请问怎么写?好像要用Group by吧
ISNULL(A.Model,B.Model) AS Model,
ISNULL(A.Shuliang,0)+ISNULL(B.Quantity,0) A Quantity
FROM tbA AS A
FULL JOIN tbB AS B
ON A.Model=B.Model
from (select Model,Quantity from A union all select Model,Shuliang as Quantity from B) t1
group by t1.Model
ISNULL(A.Model,B.Model) AS Model,
ISNULL(A.Quantity,0)+ISNULL(B.Shuliang,0) As Quantity
FROM tbA AS A
FULL JOIN tbB AS B
ON A.Model=B.Model
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @A表
DECLARE @A表 TABLE (Model VARCHAR(6),Quantity INT)
INSERT INTO @A表
SELECT 'Model1',100 UNION ALL
SELECT 'Model2',50 UNION ALL
SELECT 'Model3',80
--> 生成测试数据: @B表
DECLARE @B表 TABLE (Model VARCHAR(6),Shuliang INT)
INSERT INTO @B表
SELECT 'Model1',120 UNION ALL
SELECT 'Model3',180--SQL查询如下:SELECT
ISNULL(A.Model,B.Model) AS Model,
ISNULL(A.Quantity,0)+ISNULL(B.Shuliang,0) AS Quantity
FROM @A表 AS A
FULL JOIN @B表 AS B
ON A.Model=B.Model
/*
Model Quantity
------ -----------
Model1 220
Model2 50
Model3 260(3 行受影响)*/
select product,sum(quantity) as quantity from
(select product as product,sum(out_put) as quantity from eqp_output group by product union
select product as product,sum(qty) as quantity from defect group by product) as tblA group by product order by quantity desc
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @A表
DECLARE @A表 TABLE (Model VARCHAR(6),Quantity INT)
INSERT INTO @A表
SELECT 'Model1',100 UNION ALL
SELECT 'Model2',50 UNION ALL
SELECT 'Model3',80 UNION ALLSELECT 'Model3',60 --加入
--> 生成测试数据: @B表
DECLARE @B表 TABLE (Model VARCHAR(6),Shuliang INT)
INSERT INTO @B表
SELECT 'Model1',120 UNION ALL
SELECT 'Model3',180--SQL查询如下:SELECT
ISNULL(A.Model,B.Model) AS Model,
SUM(ISNULL(A.Quantity,0)+ISNULL(B.Shuliang,0)) AS Quantity
FROM @A表 AS A
FULL JOIN @B表 AS B
ON A.Model=B.Model
GROUP BY ISNULL(A.Model,B.Model)
/*
Model Quantity
------ -----------
Model1 220
Model2 50
Model3 500(3 行受影响)*/
楼主改一下,不要用union,要用union all,不然如果你的两个quantity正好相同的话就会只留一个,最后出来错误的结果。
具体看看union和union all的差别,我不多说了。