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;