时间戳

首页 > 解决方案 > SQL-Server Using "USE" with INNER JOINS and "UNION" together

sql - SQL-Server Using "USE" with INNER JOINS and "UNION" together

问题描述

I've seen plenty of documentation on UNION and INNER JOIN in the same query, but I cannot find documentation on this specific case.

Here's an example of what I'm trying to do, but this syntax is invalid:

USE DB1
SELECT DISTINCT ResourceTable.ID, CustomerTable.FN
FROM ResourceTable
INNER JOIN CustomerTable ON ResourceTable.CID = CustomerTable.CID
UNION ALL
USE DB2
SELECT DISTINCT ResourceTable.ID, CustomerTable.FN
FROM ResourceTable
INNER JOIN CustomerTable ON ResourceTable.CID = CustomerTable.CID
ORDER BY ResourceTable.ID

So basically, there are two databases that have identical schema but different data. I need to run an INNER JOIN query on both of them, and one way I imagined that might work is a UNION.

This would all work except for Line 6, where I am attempting to reference another database. I'm wondering if there is a way to make this work and I'm just getting the syntax wrong.

One option would be to insert both into a ##temp table and then query the temp table, but I'd like to avoid that if possible.

标签： sqlsql-serverdatabasejoin

解决方案

很容易的一个。试试下面的代码：

SELECT DISTINCT ResourceTable.ID, CustomerTable.FN
FROM DB1.dbo.ResourceTable AS ResourceTable 
INNER JOIN DB1.dbo.CustomerTable AS CustomerTable 
ON ResourceTable.CID = CustomerTable.CID
UNION ALL
SELECT DISTINCT ResourceTable.ID, CustomerTable.FN
FROM DB2.DBO.ResourceTable AS ResourceTable 
INNER JOIN DB2.DBO.CustomerTable AS CustomerTable 
ON ResourceTable.CID = CustomerTable.CID
ORDER BY ResourceTable.ID;

这是考虑到如果表在其他模式下，您正在使用默认模式“dbo”，只需将 dbo 替换为任何模式即可。

这种方式对于归档不同数据库之间的数据、比较数据或在不同数据库中的表之间进行任何连接非常有用。

推荐阅读