MS Access多表关联时筛选ANALYSIS表最新版本的优化方案问询

在MS Access环境下处理多表关联查询，需先从ANALYSIS表中筛选出每个NAME对应的最新VERSION记录，再与其余4张表关联。当前采用的子查询通过拼接NAME和VERSION字符串实现匹配，写法繁琐且效率低下，同时Access不支持(NAME, VERSION) IN (SELECT NAME, MAX(VERSION) FROM ANALYSIS GROUP BY NAME)这类多列IN的语法，因此需要更优的替代方案。

原低效子查询片段：

INNER JOIN (
    SELECT * FROM ANALYSIS WHERE
        (NAME & '/' & VERSION)  IN    (
            SELECT NAME & '/' & MAX(VERSION)
            FROM ANALYSIS
            GROUP BY NAME
         )
    ) AS a
ON
    a.NAME=ps.ANALYSIS

完整原查询语句：

SELECT
    ps.ANALYSIS AS [Analysis],
    ps.STAGE AS [Stage],
    a.ANALYSIS_TYPE AS [Anal type],
    IIF(a.CK_ANL_MTD_SPEC='',
        a.CK_ANL_MTD_GEN,
        a.CK_ANL_MTD_SPEC
    ) AS [AM],
    ps.COMPONENT AS [Components],
    SWITCH(
        ps.SPEC_RULE='Result <= MAX'       ,CHRW(8804) & ps.MAX_VALUE,
        ps.SPEC_RULE='Result >= MIN'       ,CHRW(8805) & ps.MIN_VALUE,
        ps.SPEC_RULE='MIN <= Result <= MAX',ps.MIN_VALUE & ' ' & CHRW(8212) & ' ' & ps.MAX_VALUE,
        ps.SPEC_RULE='Equal to'            ,ps.TEXT_VALUE,
        ps.SPEC_RULE='Result is One of'    ,Replace(ps.TEXT_VALUE,' ',', '),
        ps.SPEC_RULE='MIN < Result < MAX'  ,'>' & ps.MIN_VALUE & ' ' & CHRW(8212) & ' <' & ps.MAX_VALUE,
        ps.SPEC_RULE='MIN < Result <= MAX' ,'>' & ps.MIN_VALUE & ' ' & CHRW(8212) & ' ' & ps.MAX_VALUE,
        ps.SPEC_RULE='MIN <= Result < MAX' , ps.MIN_VALUE & ' ' & CHRW(8212) & ' <' & ps.MAX_VALUE,
        ps.SPEC_RULE='Not Equal to'        ,CHRW(8800) & ps.TEXT_VALUE,
        ps.SPEC_RULE='Result < MAX'        ,'<' & ps.MAX_VALUE,
        ps.SPEC_RULE='Result > MIN'        ,'>' & ps.MIN_VALUE,
        ps.SPEC_RULE='Result is Like'      ,'LIKE ' & ps.TEXT_VALUE,
        ps.SPEC_RULE='Result is Not Like'  ,'NOT LIKE ' & ps.TEXT_VALUE,
        ps.SPEC_RULE='Result is Not One of','NOT ' & Replace(ps.TEXT_VALUE,' ',', '),
    ) & IIF(ps.SPEC_RULE='','Report result','' &
        (
        SELECT
            ' ' & u.DISPLAY_STRING FROM UNITS AS u
        WHERE
            u.UNIT_CODE=ps.UNITS
        AND
            u.UNIT_CODE<>'NONE'
        )
    )  AS [Specification],
    c.CLAMP_LOW & c.CLAMP_HIGH as [Clamp],
    '' as [Comment],
    pg.REVISION_NUMBER
FROM ((((PRODUCT_SPEC AS ps
INNER JOIN PROD_GRADE_STAGE AS psg
ON
    psg.GRADE=ps.GRADE AND
    psg.PRODUCT=ps.PRODUCT AND
    psg.VERSION=ps.VERSION AND
    psg.ANALYSIS=ps.ANALYSIS AND
    psg.SAMPLING_POINT=ps.SAMPLING_POINT AND
    psg.STAGE=ps.STAGE)
INNER JOIN (
    SELECT * FROM ANALYSIS WHERE
        (NAME & '/' & VERSION)  IN    (
            SELECT NAME & '/' & MAX(VERSION)
            FROM ANALYSIS
            GROUP BY NAME
         )
    ) AS a
ON
    a.NAME=ps.ANALYSIS)
INNER JOIN COMPONENT AS c
ON
    c.ANALYSIS=a.NAME AND
    c.NAME=ps.COMPONENT AND
    c.VERSION=a.VERSION)
INNER JOIN PRODUCT_GRADE AS pg
ON
    pg.GRADE=ps.GRADE AND
    pg.PRODUCT=ps.PRODUCT AND
    pg.VERSION=ps.VERSION AND
    pg.SAMPLING_POINT=ps.SAMPLING_POINT)
WHERE
    ps.GRADE='#sGrade#' AND
    psg.GRADE='#sGrade#' AND
    ps.PRODUCT='#sSpec#' AND
    psg.PRODUCT='#sSpec#' AND
    ps.VERSION=#sVersion# AND
    psg.VERSION=#sVersion#
ORDER BY
    psg.ORDER_NUMBER,
    ps.ORDER_NUMBER

以下两种方案均兼容MS Access，且避免了低效的字符串拼接操作，性能更优：

方案1：关联子查询筛选最新版本 #

直接为每条记录判断是否为对应NAME的最大版本，逻辑清晰且高效：

INNER JOIN (
    SELECT * 
    FROM ANALYSIS AS main
    WHERE main.VERSION = (
        SELECT MAX(sub.VERSION) 
        FROM ANALYSIS AS sub
        WHERE sub.NAME = main.NAME
    )
) AS a
ON a.NAME = ps.ANALYSIS

方案2：自连接LEFT JOIN筛选最新版本 #

通过自连接对比同NAME下的版本号，保留没有更大版本的记录：

INNER JOIN (
    SELECT main.*
    FROM ANALYSIS AS main
    LEFT JOIN ANALYSIS AS sub
        ON main.NAME = sub.NAME 
        AND main.VERSION < sub.VERSION
    WHERE sub.NAME IS NULL
) AS a
ON a.NAME = ps.ANALYSIS

优化说明 #

• 两种方案均无需字符串拼接，避免了额外的计算开销
• 若在ANALYSIS表的NAME和VERSION字段上创建复合索引，查询性能会大幅提升
• 写法更简洁易读，维护性更强

将原查询中的ANALYSIS子查询替换为上述任意一种方案即可，以下是替换方案1后的完整查询：

SELECT
    ps.ANALYSIS AS [Analysis],
    ps.STAGE AS [Stage],
    a.ANALYSIS_TYPE AS [Anal type],
    IIF(a.CK_ANL_MTD_SPEC='',
        a.CK_ANL_MTD_GEN,
        a.CK_ANL_MTD_SPEC
    ) AS [AM],
    ps.COMPONENT AS [Components],
    SWITCH(
        ps.SPEC_RULE='Result <= MAX'       ,CHRW(8804) & ps.MAX_VALUE,
        ps.SPEC_RULE='Result >= MIN'       ,CHRW(8805) & ps.MIN_VALUE,
        ps.SPEC_RULE='MIN <= Result <= MAX',ps.MIN_VALUE & ' ' & CHRW(8212) & ' ' & ps.MAX_VALUE,
        ps.SPEC_RULE='Equal to'            ,ps.TEXT_VALUE,
        ps.SPEC_RULE='Result is One of'    ,Replace(ps.TEXT_VALUE,' ',', '),
        ps.SPEC_RULE='MIN < Result < MAX'  ,'>' & ps.MIN_VALUE & ' ' & CHRW(8212) & ' <' & ps.MAX_VALUE,
        ps.SPEC_RULE='MIN < Result <= MAX' ,'>' & ps.MIN_VALUE & ' ' & CHRW(8212) & ' ' & ps.MAX_VALUE,
        ps.SPEC_RULE='MIN <= Result < MAX' , ps.MIN_VALUE & ' ' & CHRW(8212) & ' <' & ps.MAX_VALUE,
        ps.SPEC_RULE='Not Equal to'        ,CHRW(8800) & ps.TEXT_VALUE,
        ps.SPEC_RULE='Result < MAX'        ,'<' & ps.MAX_VALUE,
        ps.SPEC_RULE='Result > MIN'        ,'>' & ps.MIN_VALUE,
        ps.SPEC_RULE='Result is Like'      ,'LIKE ' & ps.TEXT_VALUE,
        ps.SPEC_RULE='Result is Not Like'  ,'NOT LIKE ' & ps.TEXT_VALUE,
        ps.SPEC_RULE='Result is Not One of','NOT ' & Replace(ps.TEXT_VALUE,' ',', '),
    ) & IIF(ps.SPEC_RULE='','Report result','' &
        (
        SELECT
            ' ' & u.DISPLAY_STRING FROM UNITS AS u
        WHERE
            u.UNIT_CODE=ps.UNITS
        AND
            u.UNIT_CODE<>'NONE'
        )
    )  AS [Specification],
    c.CLAMP_LOW & c.CLAMP_HIGH as [Clamp],
    '' as [Comment],
    pg.REVISION_NUMBER
FROM ((((PRODUCT_SPEC AS ps
INNER JOIN PROD_GRADE_STAGE AS psg
ON
    psg.GRADE=ps.GRADE AND
    psg.PRODUCT=ps.PRODUCT AND
    psg.VERSION=ps.VERSION AND
    psg.ANALYSIS=ps.ANALYSIS AND
    psg.SAMPLING_POINT=ps.SAMPLING_POINT AND
    psg.STAGE=ps.STAGE)
INNER JOIN (
    SELECT * 
    FROM ANALYSIS AS main
    WHERE main.VERSION = (
        SELECT MAX(sub.VERSION) 
        FROM ANALYSIS AS sub
        WHERE sub.NAME = main.NAME
    )
) AS a
ON
    a.NAME=ps.ANALYSIS)
INNER JOIN COMPONENT AS c
ON
    c.ANALYSIS=a.NAME AND
    c.NAME=ps.COMPONENT AND
    c.VERSION=a.VERSION)
INNER JOIN PRODUCT_GRADE AS pg
ON
    pg.GRADE=ps.GRADE AND
    pg.PRODUCT=ps.PRODUCT AND
    pg.VERSION=ps.VERSION AND
    pg.SAMPLING_POINT=ps.SAMPLING_POINT)
WHERE
    ps.GRADE='#sGrade#' AND
    psg.GRADE='#sGrade#' AND
    ps.PRODUCT='#sSpec#' AND
    psg.PRODUCT='#sSpec#' AND
    ps.VERSION=#sVersion# AND
    psg.VERSION=#sVersion#
ORDER BY
    psg.ORDER_NUMBER,
    ps.ORDER_NUMBER

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