MS Access自定义交叉表查询行排序：iif与GROUP BY冲突如何解决

冲突原因 #

Access交叉表查询要求ORDER BY中引用的所有非聚合字段必须包含在GROUP BY子句中，你直接在ORDER BY中写的IIf计算项没有纳入GROUP BY列表，因此触发冲突。

方案1：新增排序字段纳入GROUP BY（最简便） #

直接将排序用的计算项同时加入SELECT和GROUP BY列表即可，修改后的完整代码如下：

PARAMETERS Forms!frm_PSFViewer!cmb_TDNo Long;
TRANSFORM Sum(PREKIT_CONTENTS.ITEM_QTY) AS SumOfITEM_QTY
SELECT 
PSF_ITEM_DETAILS.ITEM_KEY
,VENDORS.VENDOR_NAME
,ITEMS.ITEM_NO
-- 新增排序用计算字段
,IIf(VENDORS.VENDOR_NAME = 'GNC', 0, 1) AS VendorSort
FROM VENDORS 
INNER JOIN (PREKITS 
INNER JOIN ((ITEMS 
INNER JOIN PREKIT_CONTENTS 
   ON ITEMS.ITEM_ID = PREKIT_CONTENTS.ITEM_KEY) 
INNER JOIN PSF_ITEM_DETAILS 
   ON ITEMS.ITEM_ID = PSF_ITEM_DETAILS.ITEM_KEY) 
   ON PREKITS.PREKIT_ID = PREKIT_CONTENTS.PREK_KEY) 
   ON VENDORS.VENDOR_ID = PSF_ITEM_DETAILS.PRNT_VEND_KEY
WHERE ((([PREKITS].[PSF_KEY])=[Forms]![frm_PSFViewer]![cmb_TDNo]) 
   AND ((PREKITS.PREKIT)<>'ARCHWAY'))
-- GROUP BY加入对应的计算表达式
GROUP BY PSF_ITEM_DETAILS.ITEM_KEY, VENDORS.VENDOR_NAME, ITEMS.ITEM_NO, IIf(VENDORS.VENDOR_NAME = 'GNC', 0, 1)
-- 按排序字段优先排序
ORDER BY VendorSort, VENDORS.VENDOR_NAME, ITEMS.ITEM_NO
PIVOT PREKIT_CONTENTS.PREK_KEY;

如果不需要在最终结果里显示VendorSort字段，在窗体或者报表绑定的时候隐藏该列即可。

方案2：嵌套查询排序（不保留排序字段） #

如果不想在交叉表结果里出现额外字段，可以把原有交叉表作为子查询，在外层查询中实现排序：

SELECT * 
FROM (
    -- 这里放你原来未修改排序规则的交叉表查询代码
    PARAMETERS Forms!frm_PSFViewer!cmb_TDNo Long;
    TRANSFORM Sum(PREKIT_CONTENTS.ITEM_QTY) AS SumOfITEM_QTY
    SELECT 
    PSF_ITEM_DETAILS.ITEM_KEY
    ,VENDORS.VENDOR_NAME
    ,ITEMS.ITEM_NO
    FROM VENDORS 
    INNER JOIN (PREKITS 
    INNER JOIN ((ITEMS 
    INNER JOIN PREKIT_CONTENTS 
       ON ITEMS.ITEM_ID = PREKIT_CONTENTS.ITEM_KEY) 
    INNER JOIN PSF_ITEM_DETAILS 
       ON ITEMS.ITEM_ID = PSF_ITEM_DETAILS.ITEM_KEY) 
       ON PREKITS.PREKIT_ID = PREKIT_CONTENTS.PREK_KEY) 
       ON VENDORS.VENDOR_ID = PSF_ITEM_DETAILS.PRNT_VEND_KEY
    WHERE ((([PREKITS].[PSF_KEY])=[Forms]![frm_PSFViewer]![cmb_TDNo]) 
       AND ((PREKITS.PREKIT)<>'ARCHWAY'))
    GROUP BY PSF_ITEM_DETAILS.ITEM_KEY, VENDORS.VENDOR_NAME, ITEMS.ITEM_NO
    ORDER BY VENDORS.VENDOR_NAME, ITEMS.ITEM_NO
    PIVOT PREKIT_CONTENTS.PREK_KEY
) AS CrossTabResult
ORDER BY IIf(VENDOR_NAME = 'GNC',0,1), VENDOR_NAME ASC, ITEM_NO

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