clickhouse数据库，同样的分组方式、查询条件，求和的结果不一致

原因

　　clickhouse和其他数据库的不同点之一，在查询条件引用字段时，会优先取select查出来的字段，即便在字段的值中做了字符拼接，也会优先使用拼接后的字符。如下代码

select
    concat(concat(substr('2024-09',1,4),'-01-'),'2024-09') as period,
    customer_no,
    customer_name,
　　 jia_ge

from
    price
where
    period between concat(substr('2024-09',1,4),'-01') and '2024-09'
group by
    customer_no,
    customer_name,
　　 jia_ge

　　上面代码和下面代码，统计结果完全不同，下面的结果是正确的

selectperiod,
    customer_no,
    customer_name,
　　 jia_ge

from
    price
where
    period between concat(substr('2024-09',1,4),'-01') and '2024-09'
group by
　　 period,
    customer_no,
    customer_name,
　　 jia_ge

解决办法

　　给查询表增加别名，通过别名在查询条件中引用相应字段，如下代码

select
    p.period,
    p.customer_no,
    p.customer_name,
　  p.jia_ge
from
    price as p
where
    p.period between concat(substr('2024-09',1,4),'-01') and '2024-09'
group by
　　 p.period,
    p.customer_no,
    p.customer_name,
　　 p.jia_ge