MS Access无公共字段的多区间收益Top5查询合并方法问询

步骤1：改造各区间Top5查询，新增排名公共字段 #

通过子查询给每个区间的Top5结果新增1-5的排名列作为后续关联的公共键，以你给出的1个月收益查询为例，改造后代码如下：

SELECT TOP 5
    1 + (SELECT COUNT(*) 
         FROM (
             SELECT TOP 5 qryStockOverview01Month.StockID, Round(([CurrentPrice]-[1Month])/[1Month]*100,2) AS Gain01Month
             FROM qryStockOverview01Month LEFT JOIN qryStockOverviewNow ON qryStockOverview01Month.StockID = qryStockOverviewNow.StockID
             ORDER BY Round(([CurrentPrice]-[1Month])/[1Month]*100,2) DESC, qryStockOverview01Month.StockID
         ) AS temp 
         WHERE temp.Gain01Month > Round(([CurrentPrice]-[1Month])/[1Month]*100,2) 
            OR (temp.Gain01Month = Round(([CurrentPrice]-[1Month])/[1Month]*100,2) AND temp.StockID < qryStockOverview01Month.StockID)
    ) AS Rank,
    qryStockOverview01Month.StockID, 
    Round(([CurrentPrice]-[1Month])/[1Month]*100,2) AS Gain01Month
FROM qryStockOverview01Month LEFT JOIN qryStockOverviewNow ON qryStockOverview01Month.StockID = qryStockOverviewNow.StockID
ORDER BY Gain01Month DESC, qryStockOverview01Month.StockID;

按照同样逻辑，分别改造1周、3个月、6个月的Top5查询，保存为独立查询，命名参考：qryTop5_1Week、qryTop5_1Month、qryTop5_3Month、qryTop5_6Month，每个查询都包含Rank（值为1-5）、股票代码、对应区间收益三个字段。

步骤2：编写合并查询 #

以Rank字段为关联键，左连接四个改造后的查询，按你需要的格式输出结果，代码示例如下：

SELECT
    q1.StockID & " - " & q1.Gain1Week AS `Ticker1 - Gain1Week`,
    q2.StockID & " - " & q2.Gain01Month AS `Ticker2 - Gain1Month`,
    q3.StockID & " - " & q3.Gain3Month AS `Ticker3 - Gain3Month`,
    q4.StockID & " - " & q4.Gain6Month AS `Ticker4 - Gain6Month`
FROM
    (((SELECT DISTINCT Rank FROM qryTop5_1Week) AS r
    LEFT JOIN qryTop5_1Week AS q1 ON r.Rank = q1.Rank)
    LEFT JOIN qryTop5_1Month AS q2 ON r.Rank = q2.Rank)
    LEFT JOIN qryTop5_3Month AS q3 ON r.Rank = q3.Rank)
    LEFT JOIN qryTop5_6Month AS q4 ON r.Rank = q4.Rank
ORDER BY r.Rank;

如果需要把结果保存为物理表，只需要在SELECT后面加INTO 你的目标表名即可，注意调整各查询对应的股票代码、收益字段名，和你实际改造后的查询字段保持一致。

补充说明 #

• 排名逻辑里加入了StockID作为二次排序条件，避免收益相同的记录出现重复排名，保证每个查询的Rank列都是唯一的1-5值
• 若某个区间的查询结果不足5条，对应列的空缺位置会自动显示为空值，符合需求
• 如果你不需要把股票代码和收益拼接为同一列，只需要调整SELECT后的字段，分别输出股票代码和收益即可

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