zhaoyangjian724的专栏

SQL> select * from test; ID SQL_ID---------- ---------------------------------------------------------------------------------------------------- 1 aaa 2 aaa 3 aaa 4 aaa 2 bbb 1 bbb...

select vdate ,errcnt,succcnt, round(succcnt/(succcnt+errcnt),2) * 100 ||'%' from (select vdate,length(status) - length(replace(status,'失败','-')) as errcnt ,length(status) - length(replace(status,'成功'...

--提取大写字母:SQL> select * from v;A1--------------Stewie GriffinSQL> select replace(translate(A1,'1abcdefghijklmnopqrstuvwxyz','1'),' ','.') from v;REP---S.G

select * from (select rownum rn ,a.* from dba_objects a ) where rn >10 and rn

---计算按,分割列数SQL> select * from v;STR----------------10,CLARK,MANAGERSQL> select LENGTH(translate(translate(trim(str),'a0123456789','a'),'#ABCDEFGHIJKLMNOPQRSTUVWXYZ','#'))+1 from v;LENGTH(TR

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY';SQL> select * from test_1;DW_DATA------------07-JAN-0101-JAN-0007-JAN-0112-SEP-14SQL> select * from test_1 where dw_data='12-SEP-14';DW_D

SQL> select ename from emp where ename='KING';ENAME----------KING---需求 将KING 转换为如下模式:KING首选创建一个CREATE TABLE T10 AS SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <= 10;SQL> select * fro

SQL> set linesize 200SQL> set pagesize 200SQL> select * from a; ID AMT---------- ---------- 1 100 2 200 3 300 4 400SQL> select * from b; ID AMT---------- ---------- 1

drop table test1;create table test1(name varchar2(10));select * from test1;insert into test1 values(null);insert into test1 values('');--空格insert into test1 values(' ');--tab键insert int

SQL> desc emp 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- EMPNO NUMBER ENAME VARCHAR2(10) JOB VARCHAR2(10) MGR

SQL> select empno,ename,deptno,dname from emp; EMPNO ENAME DEPTNO DNAME---------- ---------- ---------- -------------------------------------------------- 7369 SMITH 20 noname

select * from t1 where object_id in (select object_id from t2 where t1.object_id=t2.object_id and rownum<10);create table t1 as select * from dba_objectscreate table t2 as select * from dba_obj

需求:找出NAME列值最多的对应ID的最大值SQL> select * from key; ID NAME---------- ---------- 1 a 2 a 3 a 2 b 3 b 4 a 99 a 10000 b 99

CREATE TABLE emp_bonus (empno INT , received DATE , TYPE INT);INSERT INTO emp_bonus VALUES( 7934, DATE '2005-5-17', 1 );INSERT INTO emp_bonus VALUES( 7934, DATE '2005-2-15', 2 );INSERT INTO emp_

+ 匹配一次及以上* 匹配0次及以上SQL> select * from a1;NAME----------abcabcdefabccccSQL> select * from a1 where name like '%abc%';NAME----------abcabcdefabccccSQL> select * from a1 where regexp

6.10 根据表中的行创建一个分隔列表---要求将emp表中的ename，用逗号间隔合并在一起显示。如：CLARK,KING,MILLERlistagg:把字符串连在一起SELECT deptno, SUM(sal) AS total_sal, listagg(ename, ',') within GROUP(ORDER BY ename) AS tot

create table a100(deptno varchar2(10),ename varchar2(100));SQL> set linesize 200SQL> set pagesize 200SQL> select * from a100;DEPTNO ENAME---------- --------------------------------...

select a.consumer,a.status,count(*) from (select /*+ parallel(t 8)*/ p.description as consumer, case t.respcode when '000000' then '成功' when '01' then '成功' else '失败' end as status...

mysql> select * from ClientLoginDevice;+--------------+| machineModel |+--------------+| Android01 || Android02 || Android03 || Androidpc || ios01 || ios02 || ios03

SQL> select ip, regexp_substr(ip,'[0-9]{1,3}',11) from machine_info;IP REGEXP_SUBSTR(IP,'[0-9]{1,3}',-------------------- ---------------------------------------------------------

mysql> SELECT -> sn,Clientsn,createdtime -> FROM -> ClientCashDetail -> WHERE -> createdTime < '2015-12-25 00:00:00' ->

mysql> select * from t2;+------+----------+------+| name | address | flag |+------+----------+------+| a | aaaaaaaa | 1 || a | bbbbbb | 1 || b | bbbbbb | 1 || b | bbbb

SQL> select 'alter system kill session '||sid||','||serial#||';' from v$session where username='TEST';'ALTERSYSTEMKILLSESSION'||SID||','||SERIAL#||';'---------------------------------------------

1 您尾号1970的账户于2015-02-28 14:50存入RMB10.00,余额为RMB18970.002 尊敬的客户，您尾号1609的账户于2015-02-28 14:50本行汇款支出RMB0.01,余额为RMB1463568.863 尊敬的客户，您尾号0795的账户于2015-02-28 14:50本行汇款支出RMB0.01,余额为RMB1321477.59需求取出第一个RMB后

分组函数:select sal,deptno,sum(sal) over (partition by deptno) from emp; ---按部门求总和SQL> select sal,deptno,sum(sal) over (partition by deptno) from emp; SAL DEPTNO SUM(SAL)OVER(PARTITIONBYDEPTNO-

SQL> select * from test2; ID1 ID2---------- ---------- 1 2 2 4 4 8 8 10 10 12 126 rows selected.ID1 相当于阻塞者ID2 相当于持有者SQL> select 阻塞者.id2 from test2 "阻塞者

[0-9] 表示0123456789 [^0-9] 表示非数字的任意字符 SQL> select * from emp2;DATA--------------------------------------------------SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450SCO

6.14 提取第n个分隔的子串REGEXP_SUBSTR函数格式如下：function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)__srcstr ：需要进行正则处理的字符串__pattern ：进行匹配的正则表达式__position ：起始位置，从第几个字符开始正则表达式匹配（默认为

---90-100的排在前面,然后按id排序create table test5(id int);beginfor i in 1 .. 10000loopinsert into test5 values(i);end loop;end;select id from test5 order by (case when id >=90 and id<=100 then 1 el

CREATE OR REPLACE VIEW v ASSELECT * FROM emp WHERE deptno != 10UNION ALLSELECT * FROM emp WHERE ename = 'WARD';SQL> set linesize 200SQL> select * from v where v.ename='WARD'; EMPNO ENAME

/*左表*/CREATE TABLE L ASSELECT 'left_1' AS str,'1' AS v FROM dual UNION ALLSELECT 'left_2','2' AS v FROM dual UNION ALLSELECT 'left_3','3' AS v FROM dual UNION ALLSELECT 'left_4','4' AS v FROM dua

SQL> select ename||q'['aa']' from emp where deptno=10;ENAME||Q'['AA'--------------CLARK'aa'KING'aa'MILLER'aa'SQL> select ename||''''||'aa'||'''' from emp where deptno=10;ENAME||''''||'

select ename,sal,sum(sal) over (partition by ename order by sal,empno) as running_totalfrom emp1order by 2按ename 汇总salover()开窗函数和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 SQL> select e.empno,

SQL> set linesize 200SQL> select * from (select a.* ,row_number() over( order by empno) rn  from emp a)where rn=1  2    3  ;     EMPNO ENAME      JOB        MGR HIREDATE     SAL       COMM    

SESSION 1：SQL> select * from v$mystat where rownum<2; SID STATISTIC# VALUE---------- ---------- ---------- 1 0 0SQL> select * from a1; ID FLAG1 FLAG2-----

SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- 7369 SMITH

..1..等值连接..equijoin..使用=符号连接,包括左右连接和全连接..2..非等值连接(non-equijoin)多表中指使用除等号..=..外的其他运算符号作为连接查询。其他符号指..、=、

---取1到3位SQL> select job,substr(job,1,3) from emp;JOB SUBSTR--------- ------CLERK CLESALESMAN SALSALESMAN SALMANAGER MANSALESMAN SALMANAGER MANMANAGER MANANALYST

select * from dt where dw_data=to_date(to_char(sysdate-1,'YYYY-MM-DD'),'YYYY-MM-DD') ---取前一天日期SQL> select * from dt where dw_data=trunc(sysdate-1,'dd');DW_DATA---------25-DEC-00S

SQL> select data,translate(data,'0123456789','##########') as num1, replace(translate(data,'0123456789','##########'),'#','') as num2 from v;DATA NUM1 NUM2-------------------- ---------- -