SQL 当一列是文本而另一列是数字时，如何在 Access 中内部连接两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2142576/
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 can I Inner Join two tables in Access when one column is a text and the other is number?
提问by Bryan
I have a program in access that is using some linked ODBC tables. I had originally had a query that contained the following INNER JOIN:
我有一个使用一些链接的 ODBC 表的访问程序。我最初有一个包含以下 INNER JOIN 的查询：
FROM Neptune_FN_Analysis
INNER JOIN Neptune_prem ON Neptune_FN_Analysis.PremID = Neptune_prem.premice_id
This worked just fine until the column Neptune_prem.premice_id got changed from a number to a text data type. So now I need a way to use an INNER JOIN on two columns when one is a number and the other is a text.
这工作得很好，直到列 Neptune_prem.premice_id 从数字更改为文本数据类型。所以现在我需要一种方法，当一列是数字而另一列是文本时，可以在两列上使用 INNER JOIN。
Please keep in mind that I am not allowed to simply change the data type in the table that the linked ODBC tables are looking at.
请记住，我不能简单地更改链接的 ODBC 表正在查看的表中的数据类型。
If this is impossible or a rediculous amount of code my other logical option would be to make a query to make a local table that I can edit with all of the same data in the Neptune_FN_Analysis table and in the code after that query edit the column I am joining with to type text. I would prefer to just modify this one SQL query if it is reasonable though.
如果这是不可能的或可笑的代码量，我的另一个逻辑选项是进行查询以创建一个本地表，我可以使用 Neptune_FN_Analysis 表中的所有相同数据进行编辑，并在该查询之后的代码中编辑列我正在加入以输入文本。如果合理，我宁愿只修改这个 SQL 查询。
采纳答案by OMG Ponies
回答by HLGEM
First I'd track down who made the change and find out why. You may have bigger probklems than just getting numbers to match. These changes aren;t made for no reason, if they changed form a numeric filed to atext filed, likely it is becasue they need to put text data into the field and now you won't be able to compare at all if you continue to use numerics. Database changes need to consider what else might be affected and this one clearly didn't. Find out who did it and why as the first step.
首先，我会追踪谁进行了更改并找出原因。您可能遇到的问题不仅仅是让数字匹配。这些更改不是无缘无故的，如果它们从数字字段更改为文本字段，可能是因为他们需要将文本数据放入字段中，现在如果你继续这样做，你将根本无法进行比较使用数字。数据库更改需要考虑其他可能会受到影响的因素，而这一点显然没有。作为第一步，找出是谁做的以及为什么这样做。
回答by Pentium10
Have you tried using CAST('abc' AS varchar(5))the numeric column in varchar?
您是否尝试过CAST('abc' AS varchar(5))在 varchar 中使用数字列？
EDITED
已编辑
You should use clngto cast the text as a number...
您应该使用clng将文本转换为数字...
