三向全外连接/表合并

Question

我想在三个表上执行FULL OUTER JOIN，合并公共行。

SELECT * FROM Users

id  Username  Fullname
==  ========  =====================
 7  iboyd     Ian Boyd
 8  nicholle  Michelle Karnac
10  jamie     Jimmy Chew

3 row(s) affected


SELECT * FROM GrobUsers

id  Username  Fullname
==  ========  =====================
 7  iboyd     Ian Steven Boyd 
 8  nicholle  Michelle Baker
 9  chris     Kris Kallme

3 row(s) affected


SELECT * FROM FrobUsers

id  Username  Fullname
==  ========  =====================
 7  ian       Ian
 9  chris     Kris K.
10  jamie     Jimmy Chew

3 row(s) affected

我想根据 id 列合并表格。

这就提出了我该如何做的问题 当对方希望冲突得到解决时 列值不同。算法 可用于解决用户名和全名之间冲突的方法是：

 if (id 相等) then 
    选择一个；我不在乎

我尝试了以下方法：

SELECT
   COALESCE(Users.id, GrobUsers.id, FrobUsers.id) AS id,
   COALESCE(Users.Username, GrobUsers.Username, FrobUsers.Username) AS Username,
   COALESCE(Users.FullName, GrobUsers.FullName, FrobUsers.FullName) AS Fullname
FROM Users
   FULL OUTER JOIN GrobUsers ON GrobUsers.id = Users.id

   FULL OUTER JOIN FrobUsers ON FrobUsers.id = .....something......

Answer 1

一个典型的技巧——使用没有意义的聚合函数。

select id, min(username), min(fullname) from (
  SELECT * FROM Users 
  union
  SELECT * FROM FrobUsers
  union
  SELECT * FROM GrobUsers  
) as foo
group by foo.id

嗯...但是它可能会从一个表中选择用户名并从另一表中选择全名。如果您仍然不在乎，请使用它，否则......也许

select id, username, fullname from (
  select id, username, fullname, takeme = row_number() over (partition by id)
  from (
    SELECT * FROM Users 
    union
    SELECT * FROM FrobUsers
    union
    SELECT * FROM GrobUsers  
  ) as foo
) as bar
where bar.takeme = 1

Answer 2

在您给出的示例中，您根本不需要联接。我希望这是一个真实的例子，而不是一个人为的例子。这里你想要做的非常简单，你根本不需要连接，也不需要 row_number。你可以这样做：

select id,Username,Fullname from Users
UNION ALL
select id,Username,Fullname from GrobUsers 
where id not in (select id from Users)
UNION ALL
select id,Username,Fullname from FrobUsers  
where id not in (select id from Users) and id not in (select id from GrobUsers)

它会给你这个：

id          Username   Fullname        
----------- ---------- -----------------
7           iboyd      Ian Boyd         
8           mkarnac    Michelle Karnac
9           kris       Kris Kallme 
10          jimmy      Jimmy Chew 

(4 row(s) affected)

这是我使用的测试用例：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
DROP TABLE [dbo].[Users]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GrobUsers]') AND type in (N'U'))
DROP TABLE [dbo].[GrobUsers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FrobUsers]') AND type in (N'U'))
DROP TABLE [dbo].[FrobUsers]
GO

CREATE TABLE [dbo].[Users](
    [Id] [int] NOT NULL,
    [Username] [nchar](50) NULL,
    [Fullname] [nchar](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[GrobUsers](
    [Id] [int] NOT NULL,
    [Username] [nchar](50) NULL,
    [Fullname] [nchar](50) NULL,
 CONSTRAINT [PK_GrobUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[FrobUsers](
    [Id] [int] NOT NULL,
    [Username] [nchar](50) NULL,
    [Fullname] [nchar](50) NULL,
 CONSTRAINT [PK_FrobUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO Users Values (7,'iboyd','Ian Boyd' )
INSERT INTO Users Values (8,'michelle','Michelle Karnac' )
INSERT INTO Users Values (10,'jimmy','Jimmy Chew' )


INSERT INTO [GrobUsers] Values (7,'iboyd','Ian Steven Boyd ' )
INSERT INTO [GrobUsers] Values (8,'michelle','Michelle Bachand' )
INSERT INTO [GrobUsers] Values (9,'kris','Kris Kallme' )

INSERT INTO [FrobUsers] Values (7,'iboyd','Ian' )
INSERT INTO [FrobUsers] Values (9,'michelle','Kris K.' )
INSERT INTO [FrobUsers] Values (10,'jimmy','Jimmy Chew' )
GO