如何用SQL将多个Access交叉表查询结果合并至单个区间（Bins）

不用嵌套多个交叉表，直接用条件聚合查询就能一次性完成所有区间的计数统计，只需要扫描源数据表一次，效率远高于嵌套查询方案。

核心实现思路 #

通过COUNT(IIF(条件, 1, NULL))或SUM(IIF(条件, 1, 0))为每个区间生成单独的统计字段，一次查询完成所有计算。

基础单表统计（无分组） #

假设你的源数据表名为YourDataTable，需要统计的数值字段是TargetField，以下是11个区间的示例SQL（你可以根据实际区间规则修改条件）：

SELECT
    -- 区间1：<10
    COUNT(IIF(TargetField < 10, 1, NULL)) AS [0-9],
    -- 区间2：10-19
    COUNT(IIF(TargetField >= 10 AND TargetField < 20, 1, NULL)) AS [10-19],
    -- 区间3：20-29
    COUNT(IIF(TargetField >= 20 AND TargetField < 30, 1, NULL)) AS [20-29],
    -- 区间4：30-39
    COUNT(IIF(TargetField >= 30 AND TargetField < 40, 1, NULL)) AS [30-39],
    -- 区间5：40-49
    COUNT(IIF(TargetField >= 40 AND TargetField < 50, 1, NULL)) AS [40-49],
    -- 区间6：50-59
    COUNT(IIF(TargetField >= 50 AND TargetField < 60, 1, NULL)) AS [50-59],
    -- 区间7：60-69
    COUNT(IIF(TargetField >= 60 AND TargetField < 70, 1, NULL)) AS [60-69],
    -- 区间8：70-79
    COUNT(IIF(TargetField >= 70 AND TargetField < 80, 1, NULL)) AS [70-79],
    -- 区间9：80-89
    COUNT(IIF(TargetField >= 80 AND TargetField < 90, 1, NULL)) AS [80-89],
    -- 区间10：90-99
    COUNT(IIF(TargetField >= 90 AND TargetField < 100, 1, NULL)) AS [90-99],
    -- 区间11：>=100
    COUNT(IIF(TargetField >= 100, 1, NULL)) AS [100+],
    -- 总计数（可选）
    COUNT(*) AS [总记录数]
FROM YourDataTable;

按分组字段统计 #

如果需要按某个字段（比如Category）分组统计区间数据，只需添加GROUP BY子句：

SELECT
    Category,
    COUNT(IIF(TargetField < 10, 1, NULL)) AS [0-9],
    COUNT(IIF(TargetField >= 10 AND TargetField < 20, 1, NULL)) AS [10-19],
    -- 其余区间字段同上...
    COUNT(IIF(TargetField >= 100, 1, NULL)) AS [100+],
    COUNT(*) AS [分组总记录数]
FROM YourDataTable
GROUP BY Category;

VBA辅助生成SQL（避免重复劳动） #

因为有11个区间，手动写SQL容易出错，你可以用VBA自动生成查询语句：

Sub GenerateBinCountQuery()
    Dim binDefs As Variant
    Dim sqlText As String
    Dim i As Integer
    
    ' 定义所有区间的显示名称和判断条件，按顺序添加11个区间
    binDefs = Array( _
        Array("[0-9]", "TargetField < 10"), _
        Array("[10-19]", "TargetField >= 10 AND TargetField < 20"), _
        Array("[20-29]", "TargetField >= 20 AND TargetField < 30"), _
        Array("[30-39]", "TargetField >= 30 AND TargetField < 40"), _
        Array("[40-49]", "TargetField >= 40 AND TargetField < 50"), _
        Array("[50-59]", "TargetField >= 50 AND TargetField < 60"), _
        Array("[60-69]", "TargetField >= 60 AND TargetField < 70"), _
        Array("[70-79]", "TargetField >= 70 AND TargetField < 80"), _
        Array("[80-89]", "TargetField >= 80 AND TargetField < 90"), _
        Array("[90-99]", "TargetField >= 90 AND TargetField < 100"), _
        Array("[100+]", "TargetField >= 100") _
    )
    
    ' 拼接SQL语句
    sqlText = "SELECT " & vbCrLf
    For i = LBound(binDefs) To UBound(binDefs)
        sqlText = sqlText & "    COUNT(IIF(" & binDefs(i)(1) & ", 1, NULL)) AS " & binDefs(i)(0) & "," & vbCrLf
    Next i
    sqlText = Left(sqlText, Len(sqlText) - 2) & vbCrLf ' 去掉最后一个逗号
    sqlText = sqlText & "FROM YourDataTable;"
    
    ' 输出到即时窗口，复制后直接在Access查询中使用
    Debug.Print sqlText
    
    ' 可选：直接创建查询对象
    ' Dim qdf As QueryDef
    ' Set qdf = CurrentDb.CreateQueryDef("BinCountQuery", sqlText)
End Sub

方案优势 #

• 仅扫描源数据表一次，相比嵌套交叉表的多次查询关联，资源占用大幅降低
• 逻辑清晰，维护成本低，修改区间规则只需调整对应条件
• 无需创建多个中间交叉表，减少数据库对象冗余

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