SQL 如何将具有不同数据类型的列上的表连接在一起？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1228639/
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
How to join tables together on columns with different datatypes?
提问by RSolberg
A Microsoft Access implementation is throwing a type mismatch error while trying to execute a macro that opens up some queries. Most of the tables are linked to a SQL Server and I need to join two of the tables together that have different datatypes.
Microsoft Access 实现在尝试执行打开某些查询的宏时抛出类型不匹配错误。大多数表都链接到 SQL Server，我需要将两个具有不同数据类型的表连接在一起。
Table A:
REFERENCE TEXT
表 A：
参考文本
Table B:
REFNO NUMBER
表 B：
参考编号
I would ordinarily want to correct the issue on the SQL Server side, but there are multiple apps hitting the same database and it would take a considerable amount of time to test all of them. Furthermore, we are in the process of completely rewriting this application and any work I do today is completely throw-away...
我通常希望更正 SQL Server 端的问题，但是有多个应用程序访问同一个数据库，并且需要大量时间来测试所有这些应用程序。此外，我们正在完全重写这个应用程序，我今天所做的任何工作都是完全丢弃......
If there is a way to make this join possible in access, I would save all kinds of time...
如果有一种方法可以使这种加入成为可能，我会节省各种时间......
回答by HansUp
Within Access you could use the CLng (or Cint) function to convert the Table A's REFERENCE values from text to number.
在 Access 中，您可以使用 CLng（或 Cint）函数将表 A 的参考值从文本转换为数字。
I would prefer to create a view of Table A in SQL Server to transform the field's data type before Access gets the data. You shouldn't need to test the view against your other existing apps. When your re-write make the view no longer useful, just discard it.
我更愿意在 SQL Server 中创建表 A 的视图，以便在 Access 获取数据之前转换字段的数据类型。您不需要针对其他现有应用测试视图。当您的重写使视图不再有用时，只需丢弃它。
回答by Praesagus
You can do the comparison in the criteria.
您可以在标准中进行比较。
SELECT [REFERENCE], [REFNO]
FROM [Table a], [Table b]
WHERE [REFERENCE]=cstr(nz([REFNO],""))
You can also do a passthrough - a query in access that executes on the sql server and returns only the data.
您还可以执行直通 - 在 sql server 上执行并仅返回数据的访问中的查询。
SELECT [REFERENCE], [REFNO]
FROM [Table a], [Table b]
WHERE [REFERENCE]=cast([REFNO] as varchar(25))
HTH
HTH
回答by shahkalpesh
What is the datatype for each of the column, you mentioned?
你提到的每一列的数据类型是什么？
If you want to compare it stringwise, you could do Cstr(myNumericColumn) = myStringColumn.
OR to compare it in numeric mode, do CLng(myStringColumn) = myNumericColumn.
如果你想按字符串比较它，你可以做Cstr(myNumericColumn) = myStringColumn.
或以数字模式进行比较，请执行CLng(myStringColumn) = myNumericColumn.
