如何在查询中加入MS-SQL和MySQL表？

I want to do a join accross a MS-SQL and MySql database.

我想在MS-SQL和MySql数据库中进行连接。

The MS-SQL query generates an index, effectively, and then I want to pull all MySQL records back that match the result of this query. (I could bring back both tables, i.e. the unfiltered data from MySQL and then filter using Linq, but this will be inefficient as I'll be pulling back loads more data than I need.)

MS-SQL查询有效地生成索引，然后我想拉回所有匹配此查询结果的MySQL记录。（我可以带回两个表，即来自MySQL的未过滤数据，然后使用Linq过滤，但这将是低效的，因为我将撤回加载的数据超出我的需要。）

The MS-SQL query is done via Linq:

MS-SQL查询是通过Linq完成的：

var fb1 = from f in db.tl_feedbacks  
          where f.timestamp >= new DateTime(fromYear, fromMonth, fromDay)
             && f.timestamp <= new DateTime(toYear, toMonth, toDay)   
          select new {f.requestID, f.tl_feedback_score.score };

This will bring back a table like this:

这将带回一个这样的表：

RequestID | score
-----------------
12345     | 1
12349     | 3
12446     | 3

etc.

等等

From this I want to return only those records from the following MySQL query that have a RequestID in the above table:

从这里我想只返回以下MySQL查询中具有上表中的RequestID的记录：

SELECT wo.WORKORDERID,
       COALESCE(ti.FIRST_NAME,'Not Assigned') AS 'Technician',
       COALESCE(cd.CATEGORYNAME, 'Not Assigned') AS Category,
       COALESCE(scd.NAME, 'Not Assigned') AS Subcategory,
       wof.UDF_CHAR1 "Office Location"  
FROM WorkOrder_Threaded wot  
INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID  
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID  
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID  
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID  
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID  
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID  
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID  
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID  
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID

i.e I only want to pull back records 12345, 12349 and 12446 in this example. Ultimately I want one single table which has the requestID, score, and the columns from the MySQL query. However, if I can get the "filtered" MySQL table back I can join the two afterwards. I just don't want to bring the MySQL back "unfiltered" as the table will be huge.

即我只想在这个例子中取回记录12345,12349和12446。最终我想要一个具有requestID，得分和MySQL查询列的单个表。但是，如果我可以获得“过滤”的MySQL表格，我可以加入这两个表格。我只是不想把MySQL带回“未经过滤”，因为这个表会很大。

1 个解决方案

#1

With the right OLEDB database drivers (I've only done this with PGSQL so I can't really advise), you can create a Linked Server in MSSQL. Here's a walkthrough, and here's another.

使用正确的OLEDB数据库驱动程序（我只使用PGSQL完成此操作，所以我无法提供建议），您可以在MSSQL中创建链接服务器。这是一个演练，这是另一个。

You can then query it using OPENQUERY as follows in MSSQL:

然后，您可以使用OPENQUERY在MSSQL中按如下方式查询它：

select * from openquery(LinkedServerDb,'select * from remotetable')

and join:

并加入：

select 
    * 
from 
    openquery(LinkedServerDb,'select * from remotetable') remoteTable
    join localTable on remotetable.someid=localtable.otherid

#1