oracle 查询汇总
1.查询所有父节点
select * from (select sys_connect_by_path(dept_name,'-')pname1,sys_connect_by_path(dept_id,'-') pname from web_dept_a start with dept_id = '001.001.045.001.005' connect by prior pre_dept_id = dept_id) where pname <> '-001.001.045.001.005'
2.分页查询
SELECT id,title,add_date,source,RELEASE_STATE FROM (SELECT DJ.id,DJ.add_date,DJ.title,DJ.source,DJ.RELEASE_STATE,ROWNUM RN FROM (SELECT id,title,add_date,source,RELEASE_STATE FROM ARTICLE where RELEASE_STATE = 'immediately' and is_del='false' and ARTICLE_CLASS_id= ? ORDER BY add_date desc) DJ ) WHERE RN BETWEEN ? AND ?;
3.查询当前节点的下一级
SELECT (select count(1) from web_dept_a a where a.PRE_DEPT_ID =b.DEPT_ID) as con,dept_id,dept_name,pre_dept_id from web_dept_a b where pre_dept_id = ?
4.分组排序获取第一条(https://blog.****.net/qq_34626493/article/details/79892189)
SELECT
*
FROM
( SELECT ROW_NUMBER () over ( partition BY ORG_ID ORDER BY LU_DATE DESC ) ROWI,WEB_PRODUCTION.* FROM WEB_PRODUCTION WHERE IS_DELETE = '0' and auditt='1' and CONTRIBUTE_ID = '5' )
WHERE
ROWI = 1
5.查询当天的数据
select * from WEB_PK where trunc(CREATE_TIME)=trunc(sysdate)
6、根据a表更新b表（https://www.****.net/gather_20/MtTaQg3sNTE0My1ibG9n.html）
UPDATE DJ_INFO_SMS_SENT b
SET ( ORG_ID,ORG_NAME) = ( SELECT b1.ORG_ID,b1.ORG_NAME FROM DJ_INFO_SMS_OUTBOX b1 WHERE b.SISMSID = b1.SISMSID )
WHERE
EXISTS (
SELECT
1
FROM
DJ_INFO_SMS_OUTBOX b1
WHERE
b.SISMSID = b1.SISMSID
)
7、月份加一（https://blog.****.net/yueyeqingshan/article/details/52710178）
add_months(to_date('2016-07-01', 'yyyy-mm-dd'), 1)
8、按组织统计
SELECT
substr( t.ORG_ID, 1, 19 ) hh,
count( t.ORG_ID ),
t.ORG_NAME
FROM
DJ_INFO_SMS_INBOX c
LEFT JOIN ( SELECT DISTINCT a.DESTADDR, a.ORG_ID, a.ORG_NAME FROM DJ_INFO_SMS_SENT a WHERE a.ORG_ID LIKE '001.001.045.001%' ) t ON c.SOURCEADDR = t.DESTADDR
GROUP BY
substr( t.ORG_ID, 1, 19 ),
t.ORG_ID,
t.ORG_NAME
9、【Oracle】将A表中有的数据，但是B表中没有的数据，插入到B表中；（https://blog.****.net/River_Continent/article/details/81169643）
1.先查询A表中有，但是B表中没有的数据；
select a.* form A a where
NO EXISTS(
select b.id from B b where a.id = b.id
);
2.将A有B没有的数据，插入到B表中；这里用的不是常规的values，而是insert into table_name +查询的记录；这种模式；如果不是全插入，可以将子查询的全查询改为部分查询；
insert into B b(
select a.* form A a where
NOT EXISTS(
select a.id from A a where a.id = b.id
)
);
9、插入id自增数据
String sql = "insert into DJSSO.sys_user(id, account, password, last_change_password_time) values(DJSSO.sys_user_id.nextval,?,?,?)";
10、复制表结构
create table d_table_name as select * from s_table_name where 1=2;
11、查询和删除重复的数据（https://blog.****.net/w405722907/article/details/72368297）
（1）查询
SELECT
a.CONTENT_ID
FROM
JC_CONTENT_EXT a
WHERE
( a.TITLE,a.RELEASE_DATE ) IN ( SELECT TITLE,RELEASE_DATE FROM JC_CONTENT_EXT GROUP BY TITLE,RELEASE_DATE HAVING count( * ) > 1 )
AND ROWID NOT IN ( SELECT min( ROWID ) FROM JC_CONTENT_EXT GROUP BY TITLE,RELEASE_DATE HAVING count( * ) > 1 )
（2）删除
DELETE
FROM
JC_CONTENT_EXT
WHERE
( TITLE, RELEASE_DATE ) IN ( SELECT TITLE, RELEASE_DATE FROM JC_CONTENT_EXT GROUP BY TITLE, RELEASE_DATE HAVING count( 1 ) > 1 )
AND ROWID NOT IN ( SELECT min( ROWID ) FROM JC_CONTENT_EXT GROUP BY TITLE, RELEASE_DATE HAVING count( 1 ) > 1 );
12、oracle 更新替换一个字段里面的指定字符串
语法
update table_name set 要更新的字段=replace(要更新的字段,'要替换的字符串'，'替换后字符串') where ……
例子
update JC_CONTENT_EXT set TITLE1=replace(TITLE1,'邓娟娟'，'替换后字符串') where CONTENT_ID =85858
13、Oracle 随机获取N条数据
select * from (select * from tablename order by dbms_random.value) where rownum< N;
14、模糊查询（查询是否包含某个字符串，全模糊查询比like快）
select CONTENT_ID from JC_CONTENT_TXT where instr(JC_CONTENT_TXT.TXT,'天') > 0;