php 我可以在列类型 INT 和 VARCHAR 之间 LEFT JOIN 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21303189/
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
Can I LEFT JOIN between column types INT and VARCHAR?
提问by Steve Church
I have been given the task to look into a issue with a MSSQL Query and I'm not SQL pro! It's not my forte′.
我的任务是调查 MSSQL 查询的问题，但我不是 SQL 专家！这不是我的强项'。
I have 2 tables in 2 different databases; ignore table references, as I have stripped them out of this query. My issue is PERSON_CODE is a integer value in one table and VARCHAR in another, I will be running this query and parsing the data directly into the Users table whilst hashing passwords in PHP.
我在 2 个不同的数据库中有 2 个表；忽略表引用，因为我已经从这个查询中删除了它们。我的问题是 PERSON_CODE 是一个表中的整数值，而另一个表中是 VARCHAR，我将运行此查询并将数据直接解析到用户表中，同时在 PHP 中散列密码。
However, because some of the fields in the Users table are character names like Jane, John and all values in the Students are integer only I receive a error because of data types.
但是，因为用户表中的某些字段是像 Jane、John 这样的字符名称，并且学生中的所有值都是整数，所以我收到了由于数据类型而导致的错误。
Below is the query I have built so far - it's very simple:
以下是我迄今为止构建的查询 - 非常简单：
SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users
ON Students.PERSON_CODE = users.username
WHERE Students.PERSON_CODE > '0'
AND users.username IS INTEGER <- How do i do this.
I need to know if there is a way of ignoring the fields which are not integers in the Users table so I can get only integer results from the Students table.
我需要知道是否有一种方法可以忽略用户表中不是整数的字段，以便我只能从学生表中获得整数结果。
Below was the full solution. Thank you Preveen.
下面是完整的解决方案。谢谢你。
SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users
ON Students.PERSON_CODE = CASE
When ISNUMERIC(users.username) = 1 THEN users.username
END
WHERE Students.PERSON_CODE > '0'
NEW UPDATES
新的更新
I have made some changes because I want the query to show only rows which are not in both tables using PERSON_COde/username as the identifier.
我进行了一些更改，因为我希望查询仅显示不在使用 PERSON_COde/username 作为标识符的两个表中的行。
SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users
ON cast(Students.PERSON_CODE as varchar(15)) = users.username
WHERE users.username = cast(Students.PERSON_CODE as varchar(15))
In a way i need a full outter join essentially I think.
在某种程度上，我认为基本上我需要一个完整的外部连接。
Table Students_________ Table users
PERSON_CODE____________ username
1______________________ 1
2______________________ 2
3______________________ 3
4______________________
5______________________
6______________________
7______________________
8______________________
With this query I then want to display the results 4,5,6,7,8
通过这个查询，我想显示结果 4,5,6,7,8
Thank you!
谢谢！
回答by t-clausen.dk
This seems the best solution to me:
这对我来说似乎是最好的解决方案：
SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN users
ON cast(Students.PERSON_CODE as varchar(10)) = users.username
WHERE Students.PERSON_CODE > 0
This way you don't need to check if username is an integer. You simply convert PERSON_CODE to a varchar(10) before comparing and problem is solved.
这样你就不需要检查用户名是否是一个整数。在比较和问题解决之前，您只需将 PERSON_CODE 转换为 varchar(10)。
回答by praveen
Try this:-
尝试这个：-
ON Students.PERSON_CODE = CASE
When ISNUMERIC(users.username) = 1 THEN users.username
END
where Clause:
where 条款：
And ISNUMERIC(users.username) = 1
回答by Sundar
You can use like this also, optimization using derived table join will avoid full table scan in sql join process in a bigger table.
您也可以这样使用，使用派生表连接进行优化将避免在更大表中的 sql 连接过程中进行全表扫描。
SELECT Students.PERSON_CODE, Students.PASSWORD
FROM Students
LEFT JOIN
(SELECT users.username FROM users
WHERE ISNUMERIC(users.username)= 1) As Myusers
ON Students.PERSON_CODE = Myusers.username
WHERE Students.PERSON_CODE > 0
回答by Irawan Soetomo
Sometimes using ISNUMERIC() won't work too well, as comma and decimal point would also be considered as numeric. I prefer to use TRY_CONVERT(int, a.var_key) = b.int_key, as TRY_CONVERT() will give NULL to ignore the non-integer values needed for the JOIN to work.
有时使用 ISNUMERIC() 效果不佳，因为逗号和小数点也会被视为数字。我更喜欢使用TRY_CONVERT(int, a.var_key) = b.int_key，因为 TRY_CONVERT() 会给 NULL 以忽略 JOIN 工作所需的非整数值。
