Amazon Athena SQL查询：匹配不同长度的enrolled_no字段

我有两张表——Enrolled Table（注册记录表）和Customer Table（客户表），需要查询Customer Table中enrolled_no字段与Enrolled Table中enrolled_no字段匹配的所有记录。已知Customer Table的enrolled_no是Enrolled Table对应字段的最后7位或8位字符。

表数据 #

Enrolled Table（注册记录表） #

Customer Table（客户表） #

期望输出 #

尝试的SQL #

WITH enrolled_table AS (  
    SELECT  
        customer_id,  
        SUBSTR(enrolled_no, -8) AS enrolled8,  
        SUBSTR(enrolled_no, -7) AS enrolled7  
    FROM enrolled  
)  
SELECT *  
FROM customer c  
WHERE (  
    c.enrolled_no IN (SELECT enrolled7 FROM enrolled_table)           
    OR  
    c.enrolled_no IN (SELECT enrolled8 FROM enrolled_table)    
)

更优写法推荐 #

写法1：JOIN关联+RIGHT函数匹配 #

SELECT c.*
FROM customer c
JOIN enrolled e 
    ON c.enrolled_no = RIGHT(e.enrolled_no, LENGTH(c.enrolled_no))
WHERE c.enrolled_no != 'na'

这种写法直接通过JOIN关联两张表，利用RIGHT函数截取Enrolled表enrolled_no的对应长度（和Customer表当前记录的enrolled_no长度一致）进行匹配，逻辑直观，避免了多次子查询扫描，性能更优，同时过滤掉无效的'na'值。

写法2：EXISTS+LIKE后缀匹配 #

SELECT c.*
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM enrolled e
    WHERE e.enrolled_no LIKE CONCAT('%', c.enrolled_no)
)
AND c.enrolled_no != 'na'

通过EXISTS子查询结合LIKE后缀匹配，只判断Enrolled表中是否存在以Customer表enrolled_no结尾的记录。EXISTS在找到匹配项后会立即停止扫描，效率较高，代码也更简洁。

写法3：简化CTE合并后缀集 #

如果偏好原有的CTE思路，可以简化为：

WITH enrolled_suffixes AS (
    SELECT SUBSTR(enrolled_no, -8) AS suffix FROM enrolled
    UNION
    SELECT SUBSTR(enrolled_no, -7) AS suffix FROM enrolled
)
SELECT c.*
FROM customer c
WHERE c.enrolled_no IN (SELECT suffix FROM enrolled_suffixes)
AND c.enrolled_no != 'na'

通过UNION合并7位和8位的后缀结果，避免两次独立子查询，减少重复扫描，同时过滤无效值。

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