SQL左连接时忽略Entity_ID中括号及后续内容的实现问题

核心逻辑 #

先清洗Case_Table的Entity_ID字段，提取括号(之前的纯ID内容，再与Source_Table进行关联。同时注意原SQL的WHERE条件会导致左连接失效，需要按需调整。

分数据库实现代码 #

MySQL/MariaDB #

利用SUBSTRING_INDEX直接截取(前的内容：

SELECT
    A.Case_ID,
    A.Entity_ID AS Original_Entity_ID,
    B.Entity_ID AS Source_Entity_ID,
    B.Source
FROM Case_Table A
LEFT JOIN Source_Table B
    ON SUBSTRING_INDEX(A.Entity_ID, '(', 1) = B.Entity_ID
-- 若要保留所有Case_Table记录，用下面的WHERE条件；只需匹配到Internal的则保留原WHERE
WHERE B.Source = 'Internal' OR B.Source IS NULL;

SQL Server #

通过CHARINDEX定位(的位置，再用LEFT截取：

SELECT
    A.Case_ID,
    A.Entity_ID AS Original_Entity_ID,
    B.Entity_ID AS Source_Entity_ID,
    B.Source
FROM Case_Table A
LEFT JOIN Source_Table B
    ON LEFT(A.Entity_ID, CASE 
        WHEN CHARINDEX('(', A.Entity_ID) > 0 THEN CHARINDEX('(', A.Entity_ID) - 1 
        ELSE LEN(A.Entity_ID) 
    END) = B.Entity_ID
WHERE B.Source = 'Internal' OR B.Source IS NULL;

Oracle #

用INSTR找(的位置，SUBSTR截取目标内容：

SELECT
    A.Case_ID,
    A.Entity_ID AS Original_Entity_ID,
    B.Entity_ID AS Source_Entity_ID,
    B.Source
FROM Case_Table A
LEFT JOIN Source_Table B
    ON SUBSTR(A.Entity_ID, 1, CASE 
        WHEN INSTR(A.Entity_ID, '(') > 0 THEN INSTR(A.Entity_ID, '(') - 1 
        ELSE LENGTH(A.Entity_ID) 
    END) = B.Entity_ID
WHERE B.Source = 'Internal' OR B.Source IS NULL;

关键提示 #

• 如果Case_Table中存在不含(的Entity_ID，必须加上判断逻辑，避免截取空值导致关联失败
• 原SQL的WHERE B.Source = 'Internal'会过滤掉左连接中未匹配到Source_Table的记录，若要保留所有Case_Table数据，一定要加上OR B.Source IS NULL

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