数据库为sybase,
sql如下:
1 select substring(LRRJG,1,7) as SSQJ,2 sum(case when RWDL_BH = '01' then 1 else 0 end) AS A,3 sum(case when RWDL_BH = '01' and XBHSJG != null then 1 else 0 end) AS B4 from ZYH_DBRW_LRNSRRW 5 where LR_DBRW_ID != null and DR = 0 and LRSJ >= '-10-01' and LRSJ <= '-11-01' 6 group by substring(LRRJG,1,7)
这样查询结果为:
sql更改为如下:
1 select AN_CODE,RES.* 2 from SM_ORGANISE b left join ( 3 select substring(LRRJG,1,7) as SSQJ, 4 sum(case when RWDL_BH = '01' then 1 else 0 end) AS A, 5 sum(case when RWDL_BH = '01' and XBHSJG != null then 1 else 0 end) AS B 6 from ZYH_DBRW_LRNSRRW 7 where LR_DBRW_ID != null and DR = 0 and LRSJ >= '-10-01' and LRSJ <= '-11-01' 8 group by substring(LRRJG,1,7) 9 ) as RES on RES.SSQJ + '0000'=AN_CODE
查询结果如下:(正常)
sql更改为如下:
1 select AN_CODE,RES.* 2 from SM_ORGANISE b left join ( 3 select substring(LRRJG,1,7) + '0000' as SSQJ, 4 sum(case when RWDL_BH = '01' then 1 else 0 end) AS A, 5 sum(case when RWDL_BH = '01' and XBHSJG != null then 1 else 0 end) AS B 6 from ZYH_DBRW_LRNSRRW 7 where LR_DBRW_ID != null and DR = 0 and LRSJ >= '-10-01' and LRSJ <= '-11-01' 8 group by substring(LRRJG,1,7)+'0000' 9 ) as RES on RES.SSQJ=AN_CODE 10 where ORGAN_CODE like '%0000'
查询结果如下:(字段名和字段值倒过来了)
不知道问什么会这样,是否跟数据库底层的运算机制有关?