对多个数据库的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5931652/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query on multiple databases
提问by Justin
I have two databases on one sql server, and I have to link two tables from one DB server to two tables in another DB server to get the info that I need. The problem is that when I try to link the two tables from the second DB server the query returns duplicates of 1000 or more. How can I run a single query on two databases? All tables have the repair_ord column in common. Can someone please help me? Thank you.
我在一个 sql 服务器上有两个数据库，我必须将一个数据库服务器中的两个表链接到另一个数据库服务器中的两个表以获取我需要的信息。问题是，当我尝试从第二个数据库服务器链接这两个表时，查询返回 1000 个或更多的重复项。如何在两个数据库上运行单个查询？所有表都具有共同的 repair_ord 列。有人可以帮帮我吗？谢谢你。
server 1 = CXADMINSERVER 2 = SAADMIN
server 1 = CXADMINSERVER 2 = SAADMIN
Here is what my query looks like so far:
这是我的查询到目前为止的样子：
SELECT RF.REPAIR_ORD,
RH.RECV_UNIT,
RH.RECV_SERIAL_NBR,
RP.FAULT_CODE,
RP.REPAIR_ACTION_CODE,
CG.TASK_CODE
FROM CXADMIN.RO_FAILURE_DTL RF,
CXADMIN.RO_HIST RH,
saadmin.sa_repair_part@elgsad rp,
saadmin.sa_code_group_task_dtl@elgsad cg
WHERE RF.REPAIR_ORD = RH.REPAIR_ORD
AND RP.REPAIR_ORD = CG.REPAIR_ORD
AND RF.FAILURE_CODE ='DISK'
AND RH.CURR_FACILITY_ID ='23'
AND RF.CREATED_DATE >'1-JUN-2010'
AND RF.CREATED_DATE < '1-JUL-2010'
AND ( CG.TASK_CODE ='PHMD'
OR CG.TASK_CODE ='PHSN'
OR CG.TASK_CODE ='CHMD'
OR CG.TASK_CODE ='CHSN')
回答by IAmTimCorey
I think the duplicates issue is not one of joining the two databases but rather in your join in the first place. I think you might need an INNER or OUTER join to handle the linking. As for getting data from two different databases, the syntax is fairly simple. You just add the server name dot the database name dot the owner name dot the table name.
我认为重复问题不在于加入两个数据库，而在于您的加入。我认为您可能需要一个 INNER 或 OUTER 连接来处理链接。至于从两个不同的数据库中获取数据，语法相当简单。您只需添加服务器名称点数据库名称点所有者名称点表名称。
For example:
例如：
SELECT firstdb.*, seconddb.*
FROM Server1.Database1.dbo.myTable AS firstdb
INNER JOIN Server2.Database2.dbo.myTable AS seconddb
ON firstdb.id = seconddb.id
In your example, it sounds like you are getting the link to work but you have a join issue on the repair_ord field. While I don't know your schema, I would guess that this link should be an INNER JOIN. If you just add both tables in the FROM statement and you don't do your WHERE statement properly, you will get into trouble like you are describing.
在您的示例中，听起来您正在使链接正常工作，但您在 repair_ord 字段上遇到了连接问题。虽然我不知道你的架构，但我猜这个链接应该是一个 INNER JOIN。如果您只是在 FROM 语句中添加两个表，并且没有正确执行 WHERE 语句，则会遇到您所描述的问题。
I would suggest that you simplify this setup and put it in a test environment (on one DB). Try the four-table join until you get it right. Then add in the complexities of multi-database calls.
我建议您简化此设置并将其放在测试环境中（在一个数据库上）。尝试四表连接，直到你做对了。然后添加多数据库调用的复杂性。
回答by Conrad Frix
If you rewrote your FROM clause to use ANSI 92 you would get this
如果你重写你的 FROM 子句以使用 ANSI 92 你会得到这个
FROM CXADMIN.RO_FAILURE_DTL RF
INNER JOIN CXADMIN.RO_HIST RH
ON RF.REPAIR_ORD = RH.REPAIR_ORD
,
saadmin.sa_repair_part@elgsad rp
INNER JOIN saadmin.sa_code_group_task_dtl@elgsad cg
ON RP.REPAIR_ORD = CG.REPAIR_ORD
It then becomes easy to see that you've created a cartesian product between RF join RHand RP JOIN CG
然后很容易看出您在RF join RH和之间创建了笛卡尔积RP JOIN CG
You need to JOIN RF to RP or CG, or RH to RP or CG
您需要将 RF 加入 RP 或 CG，或将 RH 加入 RP 或 CG
for example
例如
FROM CXADMIN.RO_FAILURE_DTL RF
INNER JOIN CXADMIN.RO_HIST RH
ON RF.REPAIR_ORD = RH.REPAIR_ORD
INNER JOIN saadmin.sa_repair_part@elgsad rp
ON RF.REPAIR_ORD = RP.REPAIR_ORD
INNER JOIN saadmin.sa_code_group_task_dtl@elgsad cg
ON RP.REPAIR_ORD = CG.REPAIR_ORD
Or if you insist on using ANSI-86 style joins you can just add AND RF.REPAIR_ORD = RP.REPAIR_ORDto your Where clause
或者，如果您坚持使用 ANSI-86 样式连接，您可以添加AND RF.REPAIR_ORD = RP.REPAIR_ORD到您的 Where 子句中
