对多个数据库的 SQL 查询 [英] SQL query on multiple databases

问题描述

我在一个 sql 服务器上有两个数据库，我必须将一个数据库服务器中的两个表链接到另一个数据库服务器中的两个表以获取我需要的信息.问题是，当我尝试从第二个数据库服务器链接这两个表时，查询返回 1000 个或更多的重复项.如何在两个数据库上运行单个查询?所有表都具有共同的 repair_ord 列.有人可以帮帮我吗?谢谢.

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.

服务器 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')

推荐答案

我认为重复问题不在于加入两个数据库，而在于您的加入.我认为您可能需要一个 INNER 或 OUTER 连接来处理链接.至于从两个不同的数据库中获取数据，语法相当简单.您只需添加服务器名称点数据库名称点所有者名称点表名称.

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.

例如:

SELECT firstdb.*, seconddb.*
FROM Server1.Database1.dbo.myTable AS firstdb
INNER JOIN Server2.Database2.dbo.myTable AS seconddb
   ON firstdb.id = seconddb.id

在您的示例中，听起来您正在使链接正常工作，但您在 repair_ord 字段上遇到了连接问题.虽然我不知道你的架构，但我猜这个链接应该是一个 INNER JOIN.如果您只是在 FROM 语句中添加两个表，而没有正确执行 WHERE 语句，则会遇到您所描述的问题.

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.

我建议您简化此设置并将其放在测试环境中(在一个数据库上).尝试四表连接，直到你做对了.然后加入多数据库调用的复杂性.

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.

这篇关于对多个数据库的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！