1、不能直接round(sum(t1.xjmj)*10000 ,2), 需要加上cast转一下:
round(cast(sum(t1.xjmj)*10000 as numeric),2) ;
2、如果想把日期格式转换为char型,也不能直接to_char(sysdate,'yyyy-mm-dd'),sysdate是识别不了的,需要改成:
to_char(CURRENT_DATE,'yyyy-mm-dd')
3、查询的列如果是关键字的话带上双引号,比如name,year,或者 加上as ,不然会报错:
ERROR: syntax error at or near "data"; ERROR: syntax error at or near "name";
4、两个表关联的字段字段类型要一致,不然会报错:
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
执行成功的脚本如下:
select '' X, '相' "name", round(cast(sum(t.xjmj)*10 as numeric),2) "DATA",to_char(CURRENT_DATE,'yyyy-mm-dd') "year" from ga t left join dl a on t.to_oid= cast(a.id as VARCHAR) group by t.jid
select '' X, '相' as name, round(cast(sum(t.xjmj)*10 as numeric),2) as DATA from ga t left join dl a on t.to_oid= cast(a.id as VARCHAR) group by t.jid