为何含布尔GROUP BY的查询在gorp SelectRow中失效，数据库Shell可正常运行？

原查询在PostgreSQL Shell中可正常运行，但通过gorp的SelectRow执行时失败，报错信息：
pq: column "product.production_date" must appear in the GROUP BY clause or be used in an aggregate function

查询需求是按两个时间区间分组统计：

• 2024-01-01之前的产品
• 2024-01-01至2024-07-01之间的产品

原查询语句：

select 
    count(sales.id) as total_sale_in_period,
    case 
        when product.production_date < '2024-01-01' then sum(product.price)
        else 0
    end as product_shipped_last_year_total_price,
    case 
        when product.production_date between '2024-01-01' and '2024-07-01' then sum(product.price) 
        else 0
    end as product_shipped_first_semester_total_price
from product 
left join sales on sales.product_id = product.id
where product.production_date between '2024-01-01' and '2024-07-01'
or product.production_date < '2024-01-01'
group by 
    product.production_date between '2024-01-01' and '2024-07-01',
    product.production_date < '2024-01-01';

原查询预期结果：

尝试过的改写（仍报错）：

select 
    count(sales.id) as total_sale_in_period,
    case 
        when product.production_date < '2024-01-01' then sum(product.price)
        else 0
    end as product_shipped_last_year_total_price,
    case 
        when product.production_date between '2024-01-01' and '2024-07-01' then sum(product.price) 
        else 0
    end as product_shipped_first_semester_total_price,
    case 
        when product.production_date < '2024-01-01' then 'last_year'
        when product.production_date between '2024-01-01' and '2024-07-01' then 'first_semester'
        else 'other'
    end as production_date_group
from product 
left join sales on sales.product_id = product.id
where product.production_date between '2024-01-01' and '2024-07-01'
or product.production_date < '2024-01-01'
group by 
    production_date_group;

问题根源是gorp对SQL语法的解析逻辑：原查询中product.production_date出现在CASE表达式的判断条件里，且未直接加入GROUP BY，尽管原生PostgreSQL支持这种写法，但gorp无法正确识别。

正确改写需调整两个核心点：

1. 让聚合函数包裹整个CASE表达式，而非CASE包裹聚合函数
2. 简化分组逻辑，避免冗余条件

改写后的查询语句 #

select 
    count(sales.id) as total_sale_in_period,
    sum(case when product.production_date < '2024-01-01' then product.price else 0 end) as product_shipped_last_year_total_price,
    sum(case when product.production_date between '2024-01-01' and '2024-07-01' then product.price else 0 end) as product_shipped_first_semester_total_price
from product 
left join sales on sales.product_id = product.id
where product.production_date < '2024-07-01'
group by 
    case 
        when product.production_date < '2024-01-01' then 'last_year'
        when product.production_date between '2024-01-01' and '2024-07-01' then 'first_semester'
        else 'other'
    end;

更简洁的版本 #

select 
    count(sales.id) as total_sale_in_period,
    sum(case when product.production_date < '2024-01-01' then product.price else 0 end) as product_shipped_last_year_total_price,
    sum(case when product.production_date between '2024-01-01' and '2024-07-01' then product.price else 0 end) as product_shipped_first_semester_total_price
from product 
left join sales on sales.product_id = product.id
where product.production_date < '2024-07-01'
group by 
    product.production_date < '2024-01-01';

关键调整说明 #

• 聚合逻辑修正：将case ... then sum(price)改为sum(case ... then price)，让production_date仅在聚合函数内部使用，无需加入GROUP BY
• 分组简化：两个时间区间互斥且覆盖所有筛选数据，用单个布尔条件分组即可，避免冗余
• WHERE子句优化：合并OR条件为product.production_date < '2024-07-01'，逻辑等价且更简洁

该改写既符合gorp的解析要求，又能保证查询结果与原查询一致。

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