查询以联接1个具有2个重叠表的表

Question

我有两个列相同的表。他们有相同的记录，也有不同的记录。

CREATE TABLE APP (
APPID int NOT NULL,
APPName varchar(255) NOT NULL,
);
CREATE TABLE HW_SERVER1 (
APPID int NOT NULL,
MachineName varchar(255) NOT NULL,
LocationCd varchar(255),
CONSTRAINT PK_HW PRIMARY KEY (APPID, MachineName)
);
CREATE TABLE HW_SERVER2 (
APPID int NOT NULL,
MachineName varchar(255) NOT NULL,
LocationCd varchar(255),
CONSTRAINT PK_HW PRIMARY KEY (APPID, MachineName)
);

服务器1

LocationCd//tr>

APPID	MachineName
1	m1	a1
1	m2	a2
2	m1	a1
2		m3	a3
3	m5	a2

如果我理解正确，你只需要union(而不是union all，因为你想删除重复项(：

select APPID, LocationCd
from hw_server1
union  -- on purpose to remove duplicates
select APPID, LocationCd
from hw_server2;

每个人都很快找到了使用UNION删除重复(而不是UNION ALL(的简单答案，但没有提到MachineName列对您也很有用。请看这个答案，它为您提供了所有3列的不同结果集：

WITH CTE_UniqueLocationCd AS
(
SELECT APPID, LocationCd
FROM HW_SERVER1
UNION -- Removes dupes
SELECT APPID, LocationCd
FROM HW_SERVER2
),
CTE_UniqueMachineName AS
(
SELECT APPID, MachineName
FROM HW_SERVER1
UNION -- Removes dupes
SELECT APPID, MachineName
FROM HW_SERVER2
)
SELECT DISTINCT -- Final removal of dupes that result from the many-to-many join on APPID
ULC.APPID, UMN.MachineName, ULC.LocationCd
FROM CTE_UniqueLocationCd AS ULC
INNER JOIN CTE_UniqueMachineName AS UMN
ON ULC.APPID = UMN.APPID

很好的问题来了解UNION ALL和UNION之间的区别，你有我的赞成票。

您可以使用union

select appid, locationcd from HW_SERVER1
union 
select appid, locationcd from HW_SERVER2

联合查询似乎是这里最直接的方法：

SELECT a.APPID, hw.LocationCd
FROM APP a INNER JOIN HW_SERVER1 hw ON hw.APPID = a.APPID
UNION
SELECT a.APPID, hw.LocationCd
FROM APP a INNER JOIN HW_SERVER2 hw ON hw.APPID = a.APPID;

注意：我执行上面的联接是为了确保我们只报告出现在APP表中的应用程序。

Answer 1

您可以连接这两个表并获取一个不同的集合。

SELECT DISTINCT APPID, LocationCd
FROM
(
SELECT APPID, LocationCd
FROM HW_SERVER1
UNION ALL
SELECT APPID, LocationCd
FROM HW_SERVER2
)