Hibernate纯sql查询结果和该sql在数据库直接查询结果不一致

问题: 今天在做一个查询的时候发现一个问题,我先在数据库实现了我需要的sql,然后我在代码中 代码:

select
    distinct d.id, d.name,
    COALESCE(c.count_num, 0),
    COALESCE(c.count_fix, 0),
    COALESCE(c.count_temp, 0) 
from
    table_temp d 
left join
    (
        select
        a.id, a.name,
        count(*) count_num ,
        sum(CASE WHEN a.type =1 THEN 1 ELSE 0 END ) count_fix  ,
        sum(CASE WHEN a.type =0 THEN 1 ELSE 0 END ) count_temp 
    from
        table_temp a 
        where
            a.ps_state != 1 
        group by
            a.id, a.name
    ) c 
        on c.id = d.id  and c.name = d.name 
where
    d.id = 1  and d.name= 'MarinJ Shao'

分析: 以上代码在数据库查询没有问题,在代码中查询结果第三个参数和后面两个参数的值完全一致; 之后将代码中的sql打印出来,完全一致,但是结果不对; 那么问题来了,哪里出问题了?简单分析是映射错误了。三个字段的列名因为没有重命名,系统自动命名,可能生成的列名其实都一样,数据库查看工具自动加了数字后缀做区分,Hibernate认不出区别,映射到同个字段去了。 之后对sql后两个字段新增命名,结果就ok了。 代码:

select
    distinct d.id, d.name,
    COALESCE(c.count_num, 0)  as totalNum ,--重命名
    COALESCE(c.count_fix, 0) as  totalFixNum ,--重命名
    COALESCE(c.count_temp, 0)   as totalTempNum --重命名
from
    table_temp d 
left join
    (
        select
        a.id, a.name,
        count(*) count_num ,
        sum(CASE WHEN a.type =1 THEN 1 ELSE 0 END ) count_fix  ,
        sum(CASE WHEN a.type =0 THEN 1 ELSE 0 END ) count_temp 
    from
        table_temp a 
     where
            a.ps_state != 1 
        group by
        a.id, a.name
    ) c 
        on c.id = d.id  and c.name = d.name 
where
    d.id = 1  and d.name= 'MarinJ Shao'

评论

暂无评论