Access VBA表单中UNION结合ORDER BY Rnd随机取数报错排查

我之前也遇到过Access这个奇葩的ORDER BY限制，简直头疼！咱们来一步步拆解你的问题～

错误原因核心 #

Access的查询引擎对ORDER BY的字段来源有非常严格的要求：它只允许你使用当前查询（最外层SELECT）明确选中的字段，或者在UNION场景下，只能用第一个SELECT里存在的字段/别名。你说已经在第一个SELECT里加了ID，但大概率是下面两种情况之一：

情况1：嵌套查询时外层SELECT未包含ID #

比如你的SQL结构大概是这样的：

SELECT [Duration of Call], 其他字段 FROM (
    SELECT ID, [Duration of Call], 其他字段 FROM 通话记录表 WHERE [Duration of Call] < 5
    UNION ALL
    SELECT ID, [Duration of Call], 其他字段 FROM 通话记录表 WHERE [Duration of Call] BETWEEN 5 AND 10
) AS 子查询
ORDER BY Rnd(-(100000*ID)*Time())

这时候外层SELECT没把ID列出来，Access就会判定ORDER BY里的ID“不在当前查询的选中字段里”——哪怕子查询里有ID也没用，因为它只认最外层查询返回的字段集合。

解决办法

有两种简单的处理方式：

• 方式一：外层SELECT加入ID字段（就算不需要显示，表单里可以忽略这个字段）：

SELECT ID, [Duration of Call], 其他字段 FROM (
    SELECT ID, [Duration of Call], 其他字段 FROM 通话记录表 WHERE [Duration of Call] < 5
    UNION ALL
    SELECT ID, [Duration of Call], 其他字段 FROM 通话记录表 WHERE [Duration of Call] BETWEEN 5 AND 10
) AS 子查询
ORDER BY Rnd(-(100000*ID)*Time())

• 方式二：子查询中预计算随机排序值，用别名排序：

SELECT [Duration of Call], 其他字段 FROM (
    SELECT [Duration of Call], 其他字段, Rnd(-(100000*ID)*Time()) AS 随机排序 FROM 通话记录表 WHERE [Duration of Call] < 5
    UNION ALL
    SELECT [Duration of Call], 其他字段, Rnd(-(100000*ID)*Time()) AS 随机排序 FROM 通话记录表 WHERE [Duration of Call] BETWEEN 5 AND 10
) AS 子查询
ORDER BY 随机排序

情况2：UNION查询中ORDER BY使用了未定义别名的复杂表达式 #

如果你的SQL是直接UNION后加ORDER BY，比如：

SELECT ID, [Duration of Call], 其他字段 FROM 通话记录表 WHERE [Duration of Call] < 5
UNION ALL
SELECT ID, [Duration of Call], 其他字段 FROM 通话记录表 WHERE [Duration of Call] BETWEEN 5 AND 10
ORDER BY Rnd(-(100000*ID)*Time())

Access对UNION后的ORDER BY表达式兼容性较差，直接写函数表达式容易触发错误。这时候只需要把表达式改成第一个SELECT里的别名即可：

SELECT ID, [Duration of Call], 其他字段, Rnd(-(100000*ID)*Time()) AS 随机排序 FROM 通话记录表 WHERE [Duration of Call] < 5
UNION ALL
SELECT ID, [Duration of Call], 其他字段, Rnd(-(100000*ID)*Time()) AS 随机排序 FROM 通话记录表 WHERE [Duration of Call] BETWEEN 5 AND 10
ORDER BY 随机排序

小优化提示 #

把随机种子里的Time()换成Now()会更靠谱，因为Time()每天都会重复相同的时间值，多次运行查询可能得到完全一样的随机结果；而Now()包含日期和时间，能保证每次的随机种子都是唯一的：

Rnd(-(100000*ID)*Now())

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