求按月至今明细并汇总所有交易（Group by with Roll Up）的实现方案

Hey there! Let's break this down step by step to get your month-to-date transaction details and use GROUP BY WITH ROLLUP for summary statistics, based on your sales table structure.

1. 第一步：筛选当月至今的交易明细 #

首先我们需要把数据范围限定在当月1号到今天，不同数据库的日期函数写法略有差异，以下是主流数据库的实现方式：

MySQL/MariaDB #

SELECT 
    SALES_ID,
    EMPLOYEE_ID,
    PRODUCT_ID,
    CUSTOMER_ID,
    SALES_AMOUNT,
    QUANTITY,
    TRANSACTION_DATE,
    SUPPLIER
FROM 
    销售表
WHERE 
    TRANSACTION_DATE >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
    AND TRANSACTION_DATE <= CURRENT_DATE;

SQL Server #

SELECT 
    SALES_ID,
    EMPLOYEE_ID,
    PRODUCT_ID,
    CUSTOMER_ID,
    SALES_AMOUNT,
    QUANTITY,
    TRANSACTION_DATE,
    SUPPLIER
FROM 
    销售表
WHERE 
    TRANSACTION_DATE >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND TRANSACTION_DATE <= GETDATE();

PostgreSQL #

SELECT 
    SALES_ID,
    EMPLOYEE_ID,
    PRODUCT_ID,
    CUSTOMER_ID,
    SALES_AMOUNT,
    QUANTITY,
    TRANSACTION_DATE,
    SUPPLIER
FROM 
    销售表
WHERE 
    TRANSACTION_DATE >= DATE_TRUNC('month', CURRENT_DATE)
    AND TRANSACTION_DATE <= CURRENT_DATE;

2. 加入GROUP BY WITH ROLLUP实现层级汇总 #

接下来我们可以用ROLLUP按指定维度生成层级汇总数据，下面以按EMPLOYEE_ID（员工）和PRODUCT_ID（产品）分组为例，你可以根据需求替换成SUPPLIER（供应商）、CUSTOMER_ID（客户）等任意组合：

MySQL/MariaDB 示例 #

SELECT 
    COALESCE(EMPLOYEE_ID, '所有员工') AS 员工ID,
    COALESCE(PRODUCT_ID, '所有产品') AS 产品ID,
    SUM(SALES_AMOUNT) AS 总销售金额,
    SUM(QUANTITY) AS 总销售数量,
    COUNT(SALES_ID) AS 交易笔数
FROM 
    销售表
WHERE 
    TRANSACTION_DATE >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
    AND TRANSACTION_DATE <= CURRENT_DATE
GROUP BY 
    EMPLOYEE_ID, PRODUCT_ID WITH ROLLUP;

SQL Server 示例 #

SELECT 
    ISNULL(CAST(EMPLOYEE_ID AS VARCHAR(20)), '所有员工') AS 员工ID,
    ISNULL(CAST(PRODUCT_ID AS VARCHAR(20)), '所有产品') AS 产品ID,
    SUM(SALES_AMOUNT) AS 总销售金额,
    SUM(QUANTITY) AS 总销售数量,
    COUNT(SALES_ID) AS 交易笔数
FROM 
    销售表
WHERE 
    TRANSACTION_DATE >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
    AND TRANSACTION_DATE <= GETDATE()
GROUP BY 
    ROLLUP(EMPLOYEE_ID, PRODUCT_ID);

PostgreSQL 示例 #

SELECT 
    COALESCE(EMPLOYEE_ID::TEXT, '所有员工') AS 员工ID,
    COALESCE(PRODUCT_ID::TEXT, '所有产品') AS 产品ID,
    SUM(SALES_AMOUNT) AS 总销售金额,
    SUM(QUANTITY) AS 总销售数量,
    COUNT(SALES_ID) AS 交易笔数
FROM 
    销售表
WHERE 
    TRANSACTION_DATE >= DATE_TRUNC('month', CURRENT_DATE)
    AND TRANSACTION_DATE <= CURRENT_DATE
GROUP BY 
    ROLLUP(EMPLOYEE_ID, PRODUCT_ID);

关键说明 #

• ROLLUP的层级逻辑：它会按照分组字段的顺序生成递进的汇总行，比如上面的例子会依次生成：每个员工+每个产品的明细统计 → 每个员工的全产品汇总 → 所有员工的全局汇总。
• 替换NULL值：用COALESCE（或SQL Server的ISNULL）把ROLLUP生成的NULL值替换成易懂的标签，让结果更直观。
• 自定义分组维度：你可以完全根据业务需求调整分组字段，比如只按供应商分组，或者同时按客户和产品分组。
• 明细+汇总的混合展示：如果需要同时查看原始交易明细和汇总行，可以用UNION ALL把明细查询和ROLLUP查询合并，ROLLUP本身只生成统计行，不包含原始明细。

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