数值型函数 --四舍五入round(x,y)对x保留y为小数--四舍五入数值 select round(23.225) from dual; --输出结果:24--四舍五入(小数点后保留1位小数)数值 select round(23.652,1)from dual; --输出结果:23.7--四舍五入(四舍五入到小数点前1位小数)数值 select round(25.2466,-1)from dual; --输出结果:30-- 返回x按精度y截取后的值 --未四舍五入的值 select trunc(23.621) from dual; --输出结果:23 select trunc(14.562,1)fr...
SELECT *FROM ( SELECT * FROM SYS_ORG START WITH org_id =1 CONNECT BY PRIOR org_id = PARENT_ID ORDER SIBLINGS BY org_sort )WHERE delete_flag = 0oracle查询父子关系语句标签:where select sys sort parent lin star rom delete 本文系统来源:https://www.cnblogs.com/gekefei/p/10869799.html
SELECT select_list 2 FROM table 3 WHERE expr operator 4 (SELECT select_list 5 FROM table);理解子查询的关键在于把子查询当作一张表来看待,外层的语句可以把内嵌的子查询返回的结果当成一张表使用,子查询结果可以作为一个虚表被使用。注意,子查询要用括号括起来 。子查询根据其返回结果可以分为单行子查询和多行子查询。 1.1 单行子查询 当子查询有单行时,可以取单行中的一个字段形成单...
4)子查询子查询就是嵌套查询,例如 select .. from (selec....) where(select....),写法不一样,但就是这样的。 5)exists 关键字exists(查询语句):数据量大时非常高效,当查询语句有结果的时候返回true否则返回false例如查询有员工的部门信息select * from dept d where exists (select * from emp e where e.deptno=d.deptno); 6)集合运算集合运算:(将查询结果合并,应用场景:应用在来自不同数据的表时) 交集:inte...
语法: select { [ distinct | all ] columns | * }[ into table_name ]from { tables | views | other select }[ where conditions][ group by columns ][ having conditions ][ order by columns ] select子句 :用于选择数据表、视图中的列 into 子句:用于将原表的结构和数据插入新表中 from 子句:用于指定数据来源,包括表,视图和其他select 语句。 where 子句:用于检索的数据进行筛选 group by 子句:用于检索结...
select org_id from oa_food_yy group by org_id having count(*)>1 查询某些字段有重复的数据:select org_id,food_id from oa_food_yy group by org_id,food_id having count(*)>1select * from oa_food_yy where (org_id,food_id) in (select org_id,food_id from oa_food_yy group by org_id,food_id having count(*)>1)Oracle查询重复字段标签:重复 select acl sof sele font ros 字段 where 本文系统来源:...
* FROM (SELECT ROW_.*, ROWNUM ROWNUM_FROM (SELECT *FROM TABLE1WHERE TABLE1_ID = XXORDER BY GMT_CREATE DESC) ROW_WHERE ROWNUM <= 20)WHERE ROWNUM_ >= 11;一般项目从页面传递的数据是:page,pagesize;写成sql语句: select * from (select rownum rn,a.* from A_MODEL a where rownum<=page*pageSize)aa where aa.rn>(page-1)*pageSize 下面是在实际项目的一条sql语句:值得注意的是在mybatis中不能用<,而要用转义字符(<)...
查询主键在哪一列 set pagesize 20 --设置页大小 set linesize 120 --设置行大小 col COLUMN_NAME for a20 --设置字段显示长度 col TABLE_NAME for a20 col OWNER for a20 col CONSTRAINT_NAME for a20select cons.owner,cons.constraint_name,cons.table_name,cons.column_name from all_cons_columns cons,all_constraints ints where cons.constraint_name = ints.constraint_name and ints.table_name=‘TABLE_NAME‘ and in...
这里要纠正一个网上很多教程说的模糊匹配不能走索引的说法,因为在看《收获,不止SQL优化》一书,里面举例说到了,并且自己也跟着例子实践了一下,确实like一些特殊情况也是可以走索引的 例子来自《收获,不止SQL优化》一书,实践准备: //建表,注意要非空数据 drop table t purge; create table t as select * from dba_objects where object_id is not null;select * from t;//更新数据并建索引,用来测试 update t set object_...
1.查看锁表进程;select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID; 2.杀掉锁表进程: 记录下SID和serial# ,分别替换掉下面的1155,39095,即可解除锁表alter system kill session ‘1155,39095‘; Oracle锁表查询与解锁标签:serial mic ssi HERE lte src 技术 mamicode oracle锁 本文系统来源:https://www.cnblogs.com/banxian-yi/p/11023729.html
select * from tableName tstart with t.pid = ‘2c948a836b4f94b4016b5000c386014b‘or t.id = ‘2c948a836b4f94b4016b5000c386014b‘connect by t.pid = prior t.id Oracle树查询(查询所有子节点包含自己)标签:table div color with col from sele start rom 本文系统来源:https://www.cnblogs.com/qing-xia/p/11024467.html
SQL Server: https://blog.csdn.net/dling8/article/details/84989794 Oracle: https://www.cnblogs.com/xgwtzg/p/6140022.html https://blog.csdn.net/kikiwhq/article/details/72728689 Date和TIMESTAMP类型互换:https://blog.csdn.net/joyksk/article/details/72844375 https://www.cnblogs.com/hanxf/p/6718189.html[Oracle123] 指定日期相关查询标签:oracle tps amp html ora sdn www art ref 本文系...
with c as (select a.trandt,sum(a.tranam) tranam from tran a group by a.trandt )--将子查询抽取出来,以后可以直接用。该方法只适用于oracle,mysql不支持 select c.trandt, sum(d.tranam)from cinner join c don c.trandt >= d.trandtgroup by c.trandt select c.trandt, sum(d.tranam)from (select a.trandt, sum(a.tranam) tranamfrom tran agroup by a.trandtorder by a.trandt) cinner join (select a.trandt, sum(a.tr...
如果报表逻辑非常复杂的话, 可以把报表逻辑放到存储过程里,加工一个全局临时表。前端查询的时候只查询临时表即可。只是第一次查询需要忍受加工的时间。 --创建存储过程,返回SYS_REFCURSOR CREATE OR REPLACE PROCEDURE P_GET_AGENT(V_AGENT_CATE IN VARCHAR2,V_PAGE IN INTEGER,O_CURSOR OUT SYS_REFCURSOR) IS BEGININSERT INTO TMP_TAB_T_AGENT(AGENT_ID)SELECT AGENT_ID FROM T_AGENT TA WHERE TA.AGENT_CATE = ...
SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "已建表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB 100,2),‘990.99‘) "使用比", F.TOTAL_BYTES "空闲空间(M)", F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 1024), 2) TOTAL_BYTES, --剩余 ...