如何基于南非13位ID字段编写SQL查询CUSTOMERS表中年龄≤45岁的客户

原有写法问题 #

你现有的SQL逻辑完全不成立：南非ID是13位字符串格式，直接将整个ID字段和日期值比较，没有用到ID内置的出生日期编码规则，不可能得到正确结果。

实现前提 #

南非13位身份证号的标准编码规则：前6位为持证人出生年月日，格式为YYMMDD（两位年份+两位月份+两位日期）。我们只需要提取这6位字符转换为合法日期，再判断年龄是否≤45即可。

适配SQL Server的实现（你原有写法用了GETDATE/DATEADD，默认是SQL Server环境） #

SELECT 
    ID, lastName, firstName
FROM 
    CUSTOMERS
WHERE 
    -- 转换前6位ID为出生日期，计算年龄是否不超过45
    CASE 
        WHEN TRY_CAST('20' + LEFT(ID,6) AS DATE) <= GETDATE() 
        THEN DATEDIFF(yy, TRY_CAST('20' + LEFT(ID,6) AS DATE), GETDATE())
        ELSE DATEDIFF(yy, TRY_CAST('19' + LEFT(ID,6) AS DATE), GETDATE())
    END <= 45
    -- 可选校验：过滤不符合13位数字格式的脏ID数据
    AND LEN(ID) = 13 
    AND ISNUMERIC(ID) = 1

逻辑说明 #

• 用TRY_CAST做日期转换，遇到非法格式的ID不会导致查询报错，脏数据会自动被排除
• 年份判断逻辑解决了19XX和20XX的边界问题：如果拼接20前缀的日期是未来日期，说明持证人是19XX年出生，自动切换为19前缀计算
• 额外的长度和数字校验可以过滤不符合标准的无效ID，不需要可以直接删除

MySQL环境适配版本 #

如果你使用的是MySQL，用以下写法即可：

SELECT 
    ID, lastName, firstName
FROM 
    CUSTOMERS
WHERE 
    TIMESTAMPDIFF(YEAR,
        CASE
            WHEN STR_TO_DATE(CONCAT('20', LEFT(ID,6)), '%Y%m%d') <= CURDATE()
            THEN STR_TO_DATE(CONCAT('20', LEFT(ID,6)), '%Y%m%d')
            ELSE STR_TO_DATE(CONCAT('19', LEFT(ID,6)), '%Y%m%d')
        END,
        CURDATE()
    ) <=45
    AND LENGTH(ID) =13
    AND ID REGEXP '^[0-9]+$'

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