SQL左连接：不同数字格式的相同账号参考匹配问题求助

你的问题出在未使用正确的字符串截取语法提取账号指定部分，直接写字段加位置属于无效SQL语法。以下是针对不同场景的正确语句：

场景1：account_ref和user_id为字符串类型 #

MySQL/MariaDB/SQL Server #

LEFT JOIN adctwebpass j 
ON SUBSTRING(e.account_ref, 1, 7) = SUBSTRING(j.user_id, 1, 7) 
AND SUBSTRING(e.account_ref, 8, 1) = SUBSTRING(j.user_id, 9, 1)

Oracle #

LEFT JOIN adctwebpass j 
ON SUBSTR(e.account_ref, 1, 7) = SUBSTR(j.user_id, 1, 7) 
AND SUBSTR(e.account_ref, 8, 1) = SUBSTR(j.user_id, 9, 1)

场景2：account_ref和user_id为数值类型 #

若字段是数值类型，需先转换为字符串再截取：

MySQL/MariaDB #

LEFT JOIN adctwebpass j 
ON SUBSTRING(CAST(e.account_ref AS CHAR), 1, 7) = SUBSTRING(CAST(j.user_id AS CHAR), 1, 7) 
AND SUBSTRING(CAST(e.account_ref AS CHAR), 8, 1) = SUBSTRING(CAST(j.user_id AS CHAR), 9, 1)

SQL Server #

LEFT JOIN adctwebpass j 
ON SUBSTRING(CAST(e.account_ref AS VARCHAR(20)), 1, 7) = SUBSTRING(CAST(j.user_id AS VARCHAR(20)), 1, 7) 
AND SUBSTRING(CAST(e.account_ref AS VARCHAR(20)), 8, 1) = SUBSTRING(CAST(j.user_id AS VARCHAR(20)), 9, 1)

Oracle #

LEFT JOIN adctwebpass j 
ON SUBSTR(TO_CHAR(e.account_ref), 1, 7) = SUBSTR(TO_CHAR(j.user_id), 1, 7) 
AND SUBSTR(TO_CHAR(e.account_ref), 8, 1) = SUBSTR(TO_CHAR(j.user_id), 9, 1)

上述语句会分别提取account_ref的前7位和第8位，与user_id的前7位和第9位进行匹配，实现你需要的关联逻辑。

内容的提问来源于stack exchange，提问作者EGL2000