oracle
IT的鱼
只求问心无愧,工作也是一种修行,活在当下
展开
-
oracle 根据逗号分割查询
SELECT v.idcard, v.contact_type_list FROM ( SELECT idcard AS idcard, substr( REPLACE ( contact_type_list, ',', ',' ), l, instr( REPLACE ( contact_type_list, ',', ',' .原创 2022-03-22 16:09:40 · 5044 阅读 · 0 评论 -
Oracle 分组统计,并显示百分比的实现
需求:在分组统计的时候,显示每个分组所占的百分比效果图:实现代码:SELECT COUNT(1) rs ,100*round(COUNT(*)/SUM(COUNT(*)) OVER(),4)||'%'FROM TB_USER tGROUP BY t.AREAORDER BY rs DESC...原创 2022-03-21 14:41:18 · 3634 阅读 · 0 评论 -
mysql 实现序号化数字中 缺失的数字
SELECT ( SELECT IFNULL(max(CAST( commodity_number AS SIGNED )),0) + 1 FROM ( SELECT commodity_number FROM sys_commodity WHERE company_id = '950872849940217856' ) AS b WHERE commodity_number < a.commodity_number ) AS sortFROM ( SELECT commodity_num.原创 2022-03-09 16:11:58 · 726 阅读 · 0 评论 -
MySql根据ID查询树结构所有父/子节点
CREATE TABLE `monitor_menu` ( `id` varchar(45) NOT NULL DEFAULT '0' COMMENT '主键ID', `menu_name` varchar(45) DEFAULT NULL COMMENT '名称', `parent_id` varchar(45) DEFAULT NULL COMMENT '父级ID', PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHAR.原创 2022-02-28 15:00:46 · 879 阅读 · 1 评论 -
存储过程
create or replace procedure demo(c_Cursor out SYS_REFCURSOR) asbegin open c_Cursor for select * from t_ows_push_flag; end demo;原创 2020-10-21 14:54:48 · 82 阅读 · 0 评论 -
oracle instr substr 根据特殊符号截取并且转多列
select * from ( select p1.first, substr(p1.last,0,instr(p1.last,'-',1,1)-1) as second_p_name, regexp_substr(p1.last,'[^-]+',2,2) as company_name from ( select substr(p.first,0,instr(p.first,...原创 2020-09-24 09:37:46 · 616 阅读 · 0 评论 -
oracle pivot 多个字段
select ot.* from (select t2.reject_reason, t.user_name, t.user_agentid, t2.puch_type, t2.puch_address, long_to_date_str(t2.create_time) as pu..原创 2020-09-10 17:25:07 · 5426 阅读 · 0 评论 -
oracle 评论回复建表
-- Create tablecreate table T_OWS_HONOR_REPLY( id VARCHAR2(64) not null, comment_id VARCHAR2(64), content CLOB, from_uid VARCHAR2(64), create_time VARCHAR2(64), from_uname VARCHAR2(64))tablespace OWS pctfree 10 initra.原创 2020-06-11 16:43:20 · 326 阅读 · 0 评论 -
Oracle 查找被锁的表和解锁
select t2.username, t2.sid, t2.serial#, t2.logon_time from v$locked_object t1, v$session t2 where t1.session_id = t2.sid order by t2.logon_time alter system kill session '1543,1...原创 2020-01-08 15:56:54 · 208 阅读 · 0 评论 -
oracle clob字段获取报错 解决方式
public static String clobToStr(Clob clob) throws Exception { String reString = ""; java.io.Reader is = clob.getCharacterStream();// 得到流 BufferedReader br = new BufferedReader...原创 2019-12-30 10:38:01 · 772 阅读 · 0 评论 -
oracle 截取分隔
select distinct (case\n" + " when a.safeguard_person like '%/%' then\n" + " regexp_substr(a.safeguard_person, '[^/]+...原创 2019-12-19 17:14:59 · 168 阅读 · 0 评论 -
Oracle 在现有时间的时间加一小时
select to_char(to_date('2019-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+ 1/24,'yyyy-mm-dd hh24:mi:ss') next_hourfrom dual;原创 2019-11-20 17:32:24 · 1335 阅读 · 0 评论 -
Oracle clob转对象转json
Clob clob = (Clob) licenseMap.get("MODEL_TABLE"); if (clob != null) { BufferedReader sr = new BufferedReader(clob.getCharacterStream()); String content = s...原创 2019-11-13 14:08:59 · 3615 阅读 · 0 评论 -
Mybatis foreach用法
select * from ("+ " select ROWNUM AS RN, t.* from("+ "select distinct *\n" + " from t_ows_flow_instance t1\n" + " ...原创 2019-11-08 14:56:10 · 208 阅读 · 0 评论 -
java实现:对一个字符串,取出重复的,只保留第一次出现的。比如aabcc,救输出:abc
package 字符串; import java.util.ArrayList;import java.util.List; public class FilterSameCode { public static void main(String[] args) { String str1 = ",,,123aaabbcddeeeeeeeeeeeeeeeeeeeeeeeeeeee...原创 2019-11-07 15:19:26 · 1364 阅读 · 0 评论