合并具有重复数据的表 [英] Merging tables with duplicate data
本文介绍了合并具有重复数据的表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
对于一个SQL Server数据仓库,我需要匹配2个包含大致相同数据的表。
For a SQL Server datawarehouse, I need to match 2 tables containing roughly the same data.
显然,它不止如此,因此重新定义任务是不是一个选择:-)
There is obviously more to it than this, so redefining the task is not an option :-)
给出两个表A和B
表A:
id | fid | type
-------------------
100 | 1 | cookies
110 | 1 | muffins
120 | 1 | muffins
表B:
id | fid | type
--------------------
a220 | 1 | muffins
b220 | 1 | muffins
合并后(在此处应用秘密IT-SQL),它应该变成
When merged (apply secret IT here - SQL), it should become
A_B:
A_id | B_id | fid | type
---------------------------
100 | NULL | 1 | cookies
110 | a220 | 1 | muffins
120 | b220 | 1 | muffins
任何使用T-SQL的解决方案都是首选,性能也不是问题。如果SSIS是一个更简单的选择,我可以接受。
Any solution using T-SQL is preferred, performance is not an issue. If SSIS is a simpler option, I can live with that.
以下是为您创建测试环境的脚本
Here is a script for creating a test environment for you to play around with.
/****** Object: Table [dbo].[B] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[B](
[id] [varchar](10) NULL,
[fid] [int] NULL,
[type] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[B] ([id], [fid], [type]) VALUES (N'a220', 1, N'muffins')
INSERT [dbo].[B] ([id], [fid], [type]) VALUES (N'b220', 1, N'muffins')
/****** Object: Table [dbo].[A] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[A](
[id] [varchar](10) NULL,
[fid] [int] NULL,
[type] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[A] ([id], [fid], [type]) VALUES (N'100', 1, N'cookies')
INSERT [dbo].[A] ([id], [fid], [type]) VALUES (N'110', 1, N'muffins')
INSERT [dbo].[A] ([id], [fid], [type]) VALUES (N'120', 1, N'muffins')
推荐答案
假设您要匹配ID的类型和顺序...
Assuming you want to match on type and the order of the IDs...
select a.id, b.id, ISNULL(a.fid,b.fid) fid, ISNULL(a.type,b.type) type
from
(select *, ROW_NUMBER() over (partition by type order by id) rn from a ) a
full outer join
(select *, ROW_NUMBER() over (partition by type order by id) rn from b ) b
on a.rn=b.rn
and a.type = b.type
order by a.id
这篇关于合并具有重复数据的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文