PostgresQL 学习记录之与oracle区别(二)

yshao81710

（9）字段取别名必须用as

Oracle -- SELECT A.COL1  A_COL1，A.COL2  A_COL2 FROM A_TABLE A

Postgresql---- SELECT A.COL1 AS A_COL1，A.COL2 AS A_COL2  FROM A_TABLE A

（10）NVL用法

Oracle --SELECT NVL(SUM(VALUE11),0) FS_VALUE1, NVL(SUM(VALUE21),0) FS_VALUE2 FROM   FIELD_SUM

Postgresql--SELECT COALESCE(SUM(VALUE11),0) AS FS_VALUE1,COALESCE(SUM(VALUE21),0) AS FS_VALUE2
FROM  FIELD_SUM

（11）TO_NUMBER用法

Oracle -- SELECT COL1 FROM A_TABLE ORDER BY TO_NUMBER(COL1)

Postgresql- select TO_NUMBER(COL1,'99G999D9S') from A_TABLE  [注：'999999' ---- 6位数为COL1字段的长度]

（12）DECODE用法

Oracle -- SELECT DECODE(ENDFLAG,'1','A','B') ENDFLAGFROM  TEST

Postgresql- SELECT (CASEENDFLAG  WHEN '1' THEN 'A'ELSE 'B' END) AS ENDFLAG FROM TEST

（13）统计 相关 用法

Oracle -- SELECT ROUND(AVG(SUM(BASICCNT1))) BASICCNT  FROM   ACCESS_INFO_SUM1_V
WHERE YEARCODE BETWEEN '200305' AND '200505'  GROUP BY SCCODE

Postgresql-- SELECT  ROUND(AVG(AIV.BASICCNT)) AS BASICCNT  FROM  (  SELECT SUM(BASICCNT1)  AS BASICCNT
  FROM   ACCESS_INFO_SUM1_V  WHERE YEARCODE BETWEEN '200305' AND '200505' GROUP BY sccode ) AIV

（14）时间计算 用法

Oracle -- SELECT CEIL(SYSDATE - TO_DATE('20051027 14:56:10','YYYYMMDD HH24:MI:SS')) AS DAYS
FROM DUAL

Postgresql-- SELECT  EXTRACT(DAY FROM (TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS') - TO_TIMESTAMP('2005-10-27 14:56:10','YYYY-MM-DD-HH24-MI-SS') ))+1 AS DAYS  FROM  DUAL

Oracle -- add_months(date, int)

Postgresql--创建函数来解决

CREATE FUNCTION add_months(date, int)
RETURNS date AS
'SELECT ($1 + ( $2::text || ''months'')::interval)::date;'
LANGUAGE 'sql'