欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

postgresql语法

程序员文章站 2022-03-26 19:18:58
...

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;










相关标签: postgresql