postgresql语法

select CURRENT_DATE from t_user；

START WITH CONNECT BY PRIOR；
WITH RECURSIVE T(DEPT_ID,DEPT_NAME,PARENT_DEPT) AS(
SELECT DEPT_ID,DEPT_NAME,PARENT_DEPT FROM T_DEPT WHERE DEPT_ID =1
UNION ALL
  SELECT T1.DEPT_ID,T1.DEPT_NAME,T1.PARENT_DEPT
   FROM T_DEPT T1 JOIN T ON T1.PARENT_DEPT=T.DEPT_ID 
)
select DEPT_ID,DEPT_NAME,PARENT_DEPT from T;


WITH RECURSIVE T(DEPT_ID,DEPT_NAME,PARENT_DEPT) AS(
SELECT DEPT_ID,DEPT_NAME,PARENT_DEPT FROM T_DEPT WHERE DEPT_ID =#{DEPT_ID} and IS_DELETE = 0 AND IS_VALID = 1
UNION ALL
  SELECT T1.DEPT_ID,T1.DEPT_NAME,T1.PARENT_DEPT
   FROM T_DEPT T1 JOIN T ON T1.PARENT_DEPT=T.DEPT_ID 
)
update "t_dept" set
            position=#{POSITION},
            update_date=current_date,
            update_user=#{LOGIN_USER_ID}      
where dept_id in (select DEPT_ID from T)；



<selectKey resultType="long" keyProperty="ID" order="BEFORE">
SELECT XT_SEQ.NEXTVAL AS ID FROM DUAL
</selectKey>
nextval('XT_SEQ'),

sysdate
current_date


AND T1.USER_ID = T2.USER_ID(+) 左连接

to_number(SITENUM)
select to_number('12121','999999999')
select trunc(243.24,0)


:: numeric

select now() - interval '5 min';

nvl(AMOUNT, 0)



WITH RECURSIVE TD(DEPT_ID) AS(
SELECT DEPT_ID FROM T_DEPT WHERE DEPT_ID =1 and IS_DELETE = 0 AND IS_VALID = 1
UNION ALL SELECT T1.DEPT_ID FROM T_DEPT T1 JOIN TD ON T1.PARENT_DEPT=TD.DEPT_ID )
select DEPT_ID from TD

level
select td.dept_id,td.parent_dept,level from t_dept td
                                where td.is_delete = 0 and td.is_valid = 1
                                start with td.dept_id =#{DEPT_ID} connect by prior td.dept_id = td.parent_dept
                                order by level


substring(SEQ_NUMBER, 1, 6)

wm_concat(USER_ID)
string_agg(name||'', ',')

自增序列取值
nextval('table_seq')

decode(user_type, 3, pos_name, decode(TA.USER_NAME, null, TA.PDA_NAME, TA.USER_NAME)) SUPERIVSOR,
select (case when usercode='zxw' then 'z' when usercode='admin' then 'a' else '' end) cod from t_user

LIKE CONCAT(CONCAT('%', 'w'),'%') 

insert语句返回id
<insert id="saveG" parameterType="map" useGeneratedKeys="true" keyProperty="rail_id">

--分页
select * from t_menu order by menu_id limit 10 offset 30

--取当前时间
select now();

--拼接多行
select string_agg(menu_id||'','-') from t_menu where menu_id <33;

--oracle 的级联查询
select * from (
WITH RECURSIVE tmp_dept (dept_id, dept_name)  AS ( 
    SELECT a.dept_id,a.dept_name
    FROM t_dept a
    WHERE a.dept_id =122
    UNION ALL 
 
    SELECT  d.dept_id,d.dept_name
    FROM t_dept d 
    JOIN tmp_dept ON d.parent_dept = tmp_dept.dept_id  
    ) 
    SELECT dept_id, dept_name FROM tmp_dept
ORDER BY dept_id) t1;