Oracle 常用sql语句

LIMIT

查1条

select * from (select rownum r,c.* from (select * from LOGININFO order by UPDATETIME DESC) c)

where r=1;

查3条

select * from adminstrator
where rowid not in(select rowid from adminstrator where rownum<=0) and rownum<=3

select * from (select a.*,rownum as rno from a) where rno>20 and rno <30;

分页：按天统计用户注册数

SELECT T2.*
FROM (
SELECT T.*, ROWNUM R
FROM (
SELECT COUNT(T1.CREATETIME ) NUMS ,TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') DATES
FROM
USERACCOUNT T1
WHERE CREATETIME BETWEEN TO_DATE('2011-05-17 13:16:16', 'YYYY-MM-DD HH24:MI:SS:') AND TO_DATE('2013-09-23 10:28:42', 'YYYY-MM-DD HH24:MI:SS:')
GROUP BY TO_CHAR(T1.CREATETIME,'YYYY-MM-DD')
ORDER BY TO_CHAR(T1.CREATETIME,'YYYY-MM-DD') )
T
)T2 WHERE T2.R>0 AND T2.R<10

在时间字段上加分钟/小时/天

分： SELECT CREATETIME,CREATETIME+10/(24*60) from LOGININFO WHERE "ID"=6009

小时：SELECT CREATETIME,CREATETIME+1/24 from LOGININFO WHERE "ID"=6009
天：SELECT CREATETIME,CREATETIME+1 from LOGININFO WHERE "ID"=6009

查时间字段加一定的时长与现在时间比：

select count(*) from LOGININFO where UPDATETIME+10/(24*60)>=sysdate

建序列

create sequence seq_name
increment by 1
start with 1
maxvalue 999999999;

查看已有序列

selectsequence_namefromALL_SEQUENCES;

selectsequence_namefromUSER_SEQUENCES;

selectsequence_namefromDBA_SEQUENCES;

删除序列

DROP SEQUENCE seq_name；

从一个表插入另一个表数据

INSERT INTO ET_USERSPACE(USERID) SELECT USERID FROM SMM_USERINFO WHERE USERID>1460 AND USERID <10000;

把SMM_USERINFO表里有而ET_USERSPACE没有的数据插入ET_USERSPACE

INSERT INTO ET_USERSPACE(USERID) SELECT USERID FROM (SELECT USERID FROM (select distinct SMM_USERINFO.USERID ,ET_USERSPACE.USERID as aa from SMM_USERINFO left join ET_USERSPACE on SMM_USERINFO.USERID = ET_USERSPACE.USERID) a WHERE a.AA IS null) b;

更多相关文章

随机推荐