合并表SQL Server 2005
|
表1：LocID，Prod_ID，Metric_ID，Metric_Data
表2：LocID，Metric_ID，Metric_Data
我需要一个结果表为：
地点编号
产品ID
Metric_ID
满足以下条件的Metric_Data：
当Metric_ID \匹配时，将添加Metric_Data
当Metric_ID不匹配时，将显示相关的Metric_Data（表示具有值的那个）
请注意，表1和表2中仅存在一些常见的Metric_ID。
您如何生成此第三张表？我尝试了各种连接-完整，左，右等。
没有找到相关结果
已邀请:
1 个回复
亨尖玛次酥
select A.LocID, A.Prod_ID, B.Metric_ID, coalesce(C.Metric_Data + D.Metric_Data, C.Metric_Data, D.Metric_Data) Metric_Data from ( select LocID, Prod_ID from table1 group by LocID, Prod_ID) A inner join ( select LocID, Metric_ID from table1 group by LocID union select LocID, Metric_ID from table2 group by LocID) B on A.LocID = B.LocID left join table1 C on C.LocID = A.LocID and C.Prod_ID = A.Prod_ID and C.Metric_ID = B.Metric_ID left join table2 D on D.LocID = A.LocID and D.Metric_ID = B.Metric_IDselect coalesce(a.LocID, b.LocID) LocID, a.Prod_ID, coalesce(a.Metric_ID, b.Metric_ID) Metric_ID, coalesce(a.Metric_Data + b.Metric_Data, a.Metric_Data, b.Metric_Data) Metric_Data from table1 a full outer join table2 b on a.LocID = b.LocID and a.Metric_ID = b.Metric_ID