如何在MS Access或SQL产品中处理Street_Address并统计X开头街道住宅建筑数？

一、该需求可在MS Access中实现 #

MS Access具备满足需求的字符串处理与聚合能力，核心逻辑分为提取街道名称、筛选匹配条件、分组统计三步，以下是针对不同地址格式的SQL示例：

场景1：Street_Address格式为「街道名 后缀」（如Xxx Avenue） #

SELECT 
    -- 提取空格前的街道名称，无空格则直接取原字段
    IIf(InStr(Street_Address, ' ') > 0, 
        Left(Street_Address, InStr(Street_Address, ' ') - 1), 
        Street_Address) AS Street_Name,
    COUNT(*) AS Residential_Building_Count
FROM Address_Population
WHERE 
    -- 筛选以X开头的街道，且类别为Residential
    (IIf(InStr(Street_Address, ' ') > 0, 
         Left(Street_Address, InStr(Street_Address, ' ') - 1), 
         Street_Address) Like 'X*')
    AND Category = 'Residential'
GROUP BY 
    IIf(InStr(Street_Address, ' ') > 0, 
        Left(Street_Address, InStr(Street_Address, ' ') - 1), 
        Street_Address);

场景2：Street_Address包含门牌号（如123 Xyz Street） #

先剥离门牌号部分再提取街道名称：

SELECT 
    IIf(InStr(Mid(Street_Address, InStr(Street_Address, ' ') + 1), ' ') > 0,
        Left(Mid(Street_Address, InStr(Street_Address, ' ') + 1), 
             InStr(Mid(Street_Address, InStr(Street_Address, ' ') + 1), ' ') - 1),
        Mid(Street_Address, InStr(Street_Address, ' ') + 1)) AS Street_Name,
    COUNT(*) AS Residential_Building_Count
FROM Address_Population
WHERE 
    (IIf(InStr(Mid(Street_Address, InStr(Street_Address, ' ') + 1), ' ') > 0,
         Left(Mid(Street_Address, InStr(Street_Address, ' ') + 1), 
              InStr(Mid(Street_Address, InStr(Street_Address, ' ') + 1), ' ') - 1),
         Mid(Street_Address, InStr(Street_Address, ' ') + 1)) Like 'X*')
    AND Category = 'Residential'
GROUP BY 
    IIf(InStr(Mid(Street_Address, InStr(Street_Address, ' ') + 1), ' ') > 0,
        Left(Mid(Street_Address, InStr(Street_Address, ' ') + 1), 
             InStr(Mid(Street_Address, InStr(Street_Address, ' ') + 1), ' ') - 1),
        Mid(Street_Address, InStr(Street_Address, ' ') + 1));

二、复杂格式场景下的替代SQL产品 #

如果Street_Address格式极不固定（如含特殊字符、多段标识），MS Access的字符串函数灵活性有限，可选择以下主流SQL产品：

MySQL #

支持REGEXP_SUBSTR()等正则函数，适合复杂模式匹配：

SELECT 
    REGEXP_SUBSTR(Street_Address, '([A-Za-z]+) (Avenue|Street|Road)') AS Street_Name,
    COUNT(*) AS Residential_Building_Count
FROM Address_Population
WHERE 
    REGEXP_SUBSTR(Street_Address, '([A-Za-z]+) (Avenue|Street|Road)') LIKE 'X%'
    AND Category = 'Residential'
GROUP BY REGEXP_SUBSTR(Street_Address, '([A-Za-z]+) (Avenue|Street|Road)');

PostgreSQL #

支持正则表达式结合SUBSTRING()，字符串处理能力极强：

SELECT 
    SUBSTRING(Street_Address FROM '([A-Za-z]+) (Avenue|Street|Road)') AS Street_Name,
    COUNT(*) AS Residential_Building_Count
FROM Address_Population
WHERE 
    SUBSTRING(Street_Address FROM '([A-Za-z]+) (Avenue|Street|Road)') LIKE 'X%'
    AND Category = 'Residential'
GROUP BY SUBSTRING(Street_Address FROM '([A-Za-z]+) (Avenue|Street|Road)');

SQL Server #

支持STRING_SPLIT()和PATINDEX()，适配多格式地址处理：

SELECT 
    LEFT(SUBSTRING(Street_Address, CHARINDEX(' ', Street_Address) + 1, LEN(Street_Address)), 
           CHARINDEX(' ', SUBSTRING(Street_Address, CHARINDEX(' ', Street_Address) + 1, LEN(Street_Address))) - 1) AS Street_Name,
    COUNT(*) AS Residential_Building_Count
FROM Address_Population
WHERE 
    LEFT(SUBSTRING(Street_Address, CHARINDEX(' ', Street_Address) + 1, LEN(Street_Address)), 
           CHARINDEX(' ', SUBSTRING(Street_Address, CHARINDEX(' ', Street_Address) + 1, LEN(Street_Address))) - 1) LIKE 'X%'
    AND Category = 'Residential'
GROUP BY 
    LEFT(SUBSTRING(Street_Address, CHARINDEX(' ', Street_Address) + 1, LEN(Street_Address)), 
           CHARINDEX(' ', SUBSTRING(Street_Address, CHARINDEX(' ', Street_Address) + 1, LEN(Street_Address))) - 1);

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