SQL中如何将分组聚合后的表与另一张表执行关联查询？

• GROUP BY 字段不符合规范：SQL 标准要求，SELECT 子句中未使用聚合函数的非聚合字段，必须全部出现在 GROUP BY 子句中。原 SQL 子查询仅按 department 分组，但 SELECT 了 date、column_i_want 两个非聚合字段，语法本身不合法，即使部分宽松的 SQL 执行环境没有报错，返回的 date、column_i_want 取值也是随机的，无法正确关联。
• 分组维度不匹配关联逻辑：关联需要同时匹配部门、日期两个维度，因此子查询的分组维度必须包含 department 和 date，才能保证子查询返回的每一行对应唯一的部门+日期组合，避免关联时出现匹配错误或者笛卡尔积。
• 冗余的聚合计算：从示例数据和预期结果来看，同部门、同日期下目标字段 column I want to join 取值固定，无需对 sales 做求和计算。

如果仅按部门、日期两个维度关联，写法如下：

SELECT t1.*, t2.column_i_want
FROM table1 AS t1
JOIN (
    SELECT department, date, column_i_want
    FROM table2
    GROUP BY department, date, column_i_want
) AS t2
ON t1.department = t2.department 
AND t1.date = t2.date

如果需要更精准的匹配，避免同部门同日期下销售额不同时匹配错误，可以把销售额也加入分组和关联条件：

SELECT t1.*, t2.column_i_want
FROM table1 AS t1
JOIN (
    SELECT department, date, sales, column_i_want
    FROM table2
    GROUP BY department, date, sales, column_i_want
) AS t2
ON t1.department = t2.department 
AND t1.date = t2.date
AND t1.sales = t2.sales

如果不需要聚合，仅做去重处理，写法更简洁：

SELECT t1.*, t2.column_i_want
FROM table1 AS t1
JOIN (
    SELECT DISTINCT department, date, column_i_want
    FROM table2
) AS t2
ON t1.department = t2.department 
AND t1.date = t2.date

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