如何在Access查询中仅返回指定字段Expr1的唯一值

你之前用DISTINCT达不到效果，是因为DISTINCT会对你SELECT子句中所有选中的字段整体做去重，只要任意字段值不同就会判定为不同行，自然无法实现仅按Expr1去重的需求。可以根据你的实际使用场景选以下方案：

方案1：仅需要获取去重后的Expr1值，不需要其他关联字段 #

直接仅查询Expr1并加DISTINCT即可，性能最优：

SELECT DISTINCT
IIf([fldC]="CAO",[fldB]+" / "+[fldF],IIf([fldC]="PAX",[fldB]+" / "+[fldE],IIf([fldC]="LTQ",[fldB]+" / "+[fldD]))) AS Expr1
FROM 
tblA LEFT JOIN tblB ON [tblA].fldA = [tblB].[fldA];

方案2：需要保留每个唯一Expr1对应的其他字段（适用于Access等支持First/Last聚合函数的数据库） #

把原有查询作为子查询，按Expr1分组，其他字段用聚合函数取对应值即可：

SELECT 
First([tblA].fldA) AS fldA,
First([tblA].fldB) AS fldB,
First([tblA].fldC) AS fldC,
First([tblB].fldD) AS fldD,
First([tblB].fldE) AS fldE,
First([tblB].fldF) AS fldF,
Expr1
FROM (
    SELECT 
    [tblA].fldA, 
    [tblA].fldB, 
    [tblA].fldC, 
    [tblB].fldD, 
    [tblB].fldE, 
    [tblB].fldF, 
    IIf([fldC]="CAO",[fldB]+" / "+[fldF],IIf([fldC]="PAX",[fldB]+" / "+[fldE],IIf([fldC]="LTQ",[fldB]+" / "+[fldD]))) AS Expr1
    FROM 
    tblA LEFT JOIN tblB ON [tblA].fldA = [tblB].[fldA]
) AS 子查询
GROUP BY Expr1;

方案3：需要保留每个唯一Expr1对应的其他字段（适用于SQL Server、MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

用ROW_NUMBER窗口函数给相同Expr1的行编号，仅取编号为1的行即可：

WITH 带行号的中间查询 AS (
    SELECT 
    [tblA].fldA, 
    [tblA].fldB, 
    [tblA].fldC, 
    [tblB].fldD, 
    [tblB].fldE, 
    [tblB].fldF, 
    IIf([fldC]="CAO",[fldB]+" / "+[fldF],IIf([fldC]="PAX",[fldB]+" / "+[fldE],IIf([fldC]="LTQ",[fldB]+" / "+[fldD]))) AS Expr1,
    ROW_NUMBER() OVER (PARTITION BY Expr1 ORDER BY [tblA].fldA) AS 行号
    FROM 
    tblA LEFT JOIN tblB ON [tblA].fldA = [tblB].[fldA]
)
SELECT fldA, fldB, fldC, fldD, fldE, fldF, Expr1
FROM 带行号的中间查询
WHERE 行号 = 1;

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