Access中用嵌套查询替换主查询依赖的多个独立子查询方法咨询

方案1：条件聚合写法（推荐，代码更简洁高效） #

无需多层嵌套连接，仅需一次关联+条件取值即可得到和原有主查询完全一致的结果，执行效率更高，维护成本更低：

SELECT 
  r.RateAirlineID,
  MAX(IIF(s.RateSlab='Min', s.NetRate, NULL)) AS MinNR,
  MAX(IIF(s.RateSlab='Min', s.SellingRate, NULL)) AS MinSR,
  MAX(IIF(s.RateSlab='-45', s.NetRate, NULL)) AS [-45NR],
  MAX(IIF(s.RateSlab='-45', s.SellingRate, NULL)) AS [-45SR],
  MAX(IIF(s.RateSlab='+45', s.NetRate, NULL)) AS [+45NR],
  MAX(IIF(s.RateSlab='+45', s.SellingRate, NULL)) AS [+45SR],
  MAX(IIF(s.RateSlab='100', s.NetRate, NULL)) AS [+100NR],
  MAX(IIF(s.RateSlab='100', s.SellingRate, NULL)) AS [+100SR],
  MAX(IIF(s.RateSlab='300', s.NetRate, NULL)) AS [+300NR],
  MAX(IIF(s.RateSlab='300', s.SellingRate, NULL)) AS [+300SR],
  MAX(IIF(s.RateSlab='500', s.NetRate, NULL)) AS [+500NR],
  MAX(IIF(s.RateSlab='500', s.SellingRate, NULL)) AS [+500SR],
  MAX(IIF(s.RateSlab='1000', s.NetRate, NULL)) AS [+1000NR],
  MAX(IIF(s.RateSlab='1000', s.SellingRate, NULL)) AS [+1000SR],
  MAX(IIF(s.RateSlab='Pivot', s.RateSlab, NULL)) AS [Pivot],
  MAX(IIF(s.RateSlab='Pivot', s.NetRate, NULL)) AS PivotNR,
  MAX(IIF(s.RateSlab='Pivot', s.SellingRate, NULL)) AS PivotSR
FROM RateAirlineTable r
LEFT JOIN RateAirlineSlabTable s ON r.RateAirlineID = s.RateAirlineid
GROUP BY r.RateAirlineID

方案2：嵌套子查询替换方案（完全匹配原有逻辑） #

将你原来的8个独立查询直接作为子查询嵌套在连接语句中，保留原有逻辑不变，替换后可直接运行：

SELECT RateAirlineTable.RateAirlineID, RateMinQuery.NetRate AS MinNR, RateMinQuery.SellingRate AS MinSR, [Rate-45Query].NetRate AS [-45NR], [Rate-45Query].SellingRate AS [-45SR], [Rate+45Query].NetRate AS [+45NR], [Rate+45Query].SellingRate AS [+45SR], [Rate+100Query].NetRate AS [+100NR], [Rate+100Query].SellingRate AS [+100SR], [Rate+300Query].NetRate AS [+300], [Rate+300Query].SellingRate AS [+300SR], [Rate+500Query].NetRate AS [+500NR], [Rate+500Query].SellingRate AS [+500SR], [Rate+1000Query].NetRate AS [+1000NR], [Rate+1000Query].SellingRate AS [+1000SR], RatePivotQuery.RateSlab AS [Pivot], RatePivotQuery.NetRate AS PivotNR, RatePivotQuery.SellingRate AS PivotSR 
FROM 
(SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='Pivot') AS RatePivotQuery
RIGHT JOIN (
  (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='1000') AS [Rate+1000Query]
  RIGHT JOIN (
    (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='500') AS [Rate+500Query]
    RIGHT JOIN (
      (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='300') AS [Rate+300Query]
      RIGHT JOIN (
        (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='100') AS [Rate+100Query]
        RIGHT JOIN (
          (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='+45') AS [Rate+45Query]
          RIGHT JOIN (
            (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='-45') AS [Rate-45Query]
            RIGHT JOIN (
              (SELECT RateAirlineid, RateSlab, NetRate, SellingRate FROM RateAirlineSlabTable WHERE RateSlab='Min') AS RateMinQuery
              RIGHT JOIN RateAirlineTable 
              ON RateMinQuery.RateAirlineid = RateAirlineTable.RateAirlineID
            ) ON [Rate-45Query].RateAirlineid = RateAirlineTable.RateAirlineID
          ) ON [Rate+45Query].RateAirlineid = RateAirlineTable.RateAirlineID
        ) ON [Rate+100Query].RateAirlineid = RateAirlineTable.RateAirlineID
      ) ON [Rate+300Query].RateAirlineid = RateAirlineTable.RateAirlineID
    ) ON [Rate+500Query].RateAirlineid = RateAirlineTable.RateAirlineID
  ) ON [Rate+1000Query].RateAirlineid = RateAirlineTable.RateAirlineID
) ON RatePivotQuery.RateAirlineid = RateAirlineTable.RateAirlineID;

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