Access多列独立排序生成Q-Q图数据失败问题求助

嘿，我看你在为Q-Q图整理数据时，遇到了用带wautonumber的子查询内连接却没返回结果的问题——单独跑子查询没问题，连起来就不行，对吧？咱们先拆解下问题，再给你几个可行的解决办法。

问题出在哪？ #

你想要把Au_org_gpt和Au_mts_gpt两列各自独立排序后，按行号一一对应，就像你举的例子那样：把6|10、8|7、2|5转成2|5、6|7、8|10。但你的SQL没返回结果，大概率是这几个原因：

• Access的子查询里，ORDER BY其实不会改变子查询传给外部查询的行顺序，你用的wautonumber函数可能是基于原表的行号生成的，不是排序后的结果，导致两个子查询的index_no根本对应不上
• 两个子查询都带了Project列，排序后不同子查询里的Project值可能乱掉了，虽然你连接用的是index_no，但如果wautonumber的逻辑和Project绑定，也会让行号不匹配
• 说不定wautonumber这个自定义函数本身的实现有问题，比如在排序后的数据集里没正确重置计数

给你几个解决方案 #

方案1：用Access的窗口函数（适合Access 2010及以上版本） #

如果你的Access版本支持窗口函数，直接用ROW_NUMBER()生成排序后的行号就靠谱多了，它能确保行号是严格按照你指定的列排序后生成的：

SELECT 
    ORG.index_no,
    ORG.Project,
    ORG.Au_org_gpt AS Au_org_gpt_QQ,
    MTS.Au_mts_gpt AS Au_mts_gpt_QQ
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Au_org_gpt) AS index_no,
        Project,
        Au_org_gpt
    FROM MCIPLD_geochem_mts
) AS ORG
INNER JOIN (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Au_mts_gpt) AS index_no,
        Au_mts_gpt
    FROM MCIPLD_geochem_mts
) AS MTS ON ORG.index_no = MTS.index_no;

方案2：修复自定义wautonumber的使用方式 #

如果你必须用wautonumber函数，那得先把排序后的结果存进临时表，再生成行号——这样能保证函数是基于排序后的记录来计数的：

第一步：先存排序后的Au_org_gpt数据到临时表

SELECT Au_org_gpt, Project INTO temp_org 
FROM MCIPLD_geochem_mts 
ORDER BY Au_org_gpt;

第二步：再存排序后的Au_mts_gpt数据到临时表

SELECT Au_mts_gpt INTO temp_mts 
FROM MCIPLD_geochem_mts 
ORDER BY Au_mts_gpt;

第三步：对临时表生成行号再连接

SELECT 
    ORG.index_no,
    ORG.Project,
    ORG.Au_org_gpt AS Au_org_gpt_QQ,
    MTS.Au_mts_gpt AS Au_mts_gpt_QQ
FROM (
    SELECT wautonumber(Au_org_gpt) AS index_no, Project, Au_org_gpt 
    FROM temp_org
) AS ORG
INNER JOIN (
    SELECT wautonumber(Au_mts_gpt) AS index_no, Au_mts_gpt 
    FROM temp_mts
) AS MTS ON ORG.index_no = MTS.index_no;

第四步：用完临时表可以删掉（可选）

DROP TABLE temp_org;
DROP TABLE temp_mts;

几个要注意的点 #

• 确保两个列的非空行数一致：如果其中一列有NULL值，记得在子查询里加WHERE Au_org_gpt IS NOT NULL或者WHERE Au_mts_gpt IS NOT NULL过滤掉，不然内连接会把不匹配的行都筛掉
• 要是wautonumber是靠记录读取顺序生成的，那直接在带ORDER BY的子查询里调用肯定不行——Access子查询的ORDER BY只影响自己的显示顺序，不影响外部查询读取记录的顺序，临时表就能解决这个问题

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