MS Access中筛选单一商品类型收据并实现数据聚合的SQL查询需求

一、查询仅含单一商品类型的收据（以仅书籍为例） #

要筛选出只包含书籍的收据，核心是先确定每个收据对应的商品类型总数为1，且类型为书籍。由于MS Access不支持COUNT(DISTINCT)，可以通过子查询先对每个收据的商品类型去重，再统计类型数量：

-- 获取仅含书籍的收据销售统计
SELECT t1.[RECEIPT ID], SUM(t1.[QUANTITY]) AS QNT, SUM(t1.[VALUE]) AS SALESV
FROM [TABLEA] t1
INNER JOIN (
    -- 子查询：筛选出只有一种商品类型的收据ID
    SELECT [RECEIPT ID]
    FROM (
        -- 对每个收据的商品类型去重
        SELECT DISTINCT [RECEIPT ID], PRODUCT
        FROM [TABLEA]
    ) AS t2
    GROUP BY [RECEIPT ID]
    HAVING COUNT(*) = 1
) AS t3 ON t1.[RECEIPT ID] = t3.[RECEIPT ID]
WHERE t1.PRODUCT = 'BOOK'
GROUP BY t1.[RECEIPT ID];

若要查询仅含非书籍商品的收据，只需将WHERE条件改为PRODUCT <> 'BOOK'即可。

二、查询包含两种商品类型的混合收据 #

要筛选同时包含书籍和非书籍的收据，同样通过子查询统计每个收据的商品类型数量为2，再关联原表计算总和：

1. 混合收据整体销售统计 #

SELECT t1.[RECEIPT ID], SUM(t1.[QUANTITY]) AS TOTAL_QNT, SUM(t1.[VALUE]) AS TOTAL_SALES
FROM [TABLEA] t1
INNER JOIN (
    SELECT [RECEIPT ID]
    FROM (
        SELECT DISTINCT [RECEIPT ID], PRODUCT
        FROM [TABLEA]
    ) AS t2
    GROUP BY [RECEIPT ID]
    HAVING COUNT(*) = 2
) AS t3 ON t1.[RECEIPT ID] = t3.[RECEIPT ID]
GROUP BY t1.[RECEIPT ID];

2. 混合收据分类型统计 #

如果需要分别统计混合收据中书籍和非书籍的数量与金额，可使用条件求和：

SELECT 
    [RECEIPT ID],
    SUM(IIF(PRODUCT = 'BOOK', [QUANTITY], 0)) AS BOOK_QNT,
    SUM(IIF(PRODUCT = 'BOOK', [VALUE], 0)) AS BOOK_SALES,
    SUM(IIF(PRODUCT <> 'BOOK', [QUANTITY], 0)) AS NON_BOOK_QNT,
    SUM(IIF(PRODUCT <> 'BOOK', [VALUE], 0)) AS NON_BOOK_SALES
FROM [TABLEA]
WHERE [RECEIPT ID] IN (
    SELECT [RECEIPT ID]
    FROM (
        SELECT DISTINCT [RECEIPT ID], PRODUCT
        FROM [TABLEA]
    ) AS t2
    GROUP BY [RECEIPT ID]
    HAVING COUNT(*) = 2
)
GROUP BY [RECEIPT ID];

原SQL问题说明 #

你之前的SQL仅筛选了商品为书籍的行，但混合类型收据（比如RD226）里的书籍行也会被纳入统计，导致结果包含混合收据的部分数据，无法区分纯书籍收据和混合收据。因此需要先通过子查询筛选出符合类型条件的收据ID，再关联原表统计对应数据。

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