英文:
Compare 2 tables in 2 databases
问题
我正在尝试比较两个具有完全相同列的表，在两个不同的数据库中。这些数据库位于同一台机器上，都是SQL数据库。我试图从表2中返回在表1中不存在的结果。重要的列名为Stock。
假设名称如下。
DB1和DB2分别是这两个数据库的名称。
两个表的表名都是Vendor。
列名是Stock。
如果我使用这个示例，我得不到任何结果：
Select * From DB2.Vendor
Where (Stock != Stock)
Except
Select * From DB1.Vendor
第二个想法（这会导致错误）：
Select * From DB2.Vendor
Union
Select * From DB1.Vendor
Group by Vendor.Stock
如果您有其他建议，那就太好了。
提前感谢。
英文:
I'm trying to compare 2 table that have all the same columns in them in 2 different databases.
The databases are on the same machine, and this is all in SQL database.
I'm trying to retun results from table 2 that do not exist in table 1.
The important column is called Stock.
Lets assume the names as are follows.
DB1 and DB2 as the 2 databases.
The table name for both is Vendor
THe column is Stock
If I use this example I get no results
Select * From DB2.Vendor
Where (Stock != Stock)
Except
Select * From DB1.Vendor
Second Idea (this gives me an error)
Select * From DB2.Vendor
Union
Select * From DB1.Vendor
Group by Vendor.Stock
If you have a diffrent suggestion great
Thanks in advance.
答案1
得分: 0
左连接
选择 a.*
从 TestDB2.dbo.Vendor a
左连接 TestDB1.dbo.Vendor b ON a.Stock=b.Stock
其中 b.Stock 是 空
存在
选择 *
从 TestDB2.dbo.Vendor a
其中 不存在 (选择 * 从 TestDB1.dbo.Vendor b WHERE a.Stock=b.Stock )
英文:
You can Left join or Exists
Left join
select a.*
from TestDB2.dbo.Vendor a
left join TestDB1.dbo.Vendor b on a.Stock=b.Stock
where b.Stock is null
Exists
select *
from TestDB2.dbo.Vendor a
where not exists (select * from TestDB1.dbo.Vendor b where a.Stock=b.Stock )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论