常用sql2

查看重复值 

SELECT deptno from emp WHERE deptno in (SELECT deptno FROM emp group by deptno having count(deptno)>1);
select p1.* from test p1,test p2 where p1.rowid<>p2.rowid and p1.id=p2.id and p1.name=p2.name and p1.memo=p2.memo;

----------------------------------------------------------------------------------------------------------------------------------

查询含有小写的零件

select name from s_prod_int where Name <> upper(Name)  and name<>lower(name) and prod_cd='零件';  含有小写，但是不包含全部都是小写

select name from s_prod_int where Name <> upper(Name) and prod_cd='零件'; 含有小写，含有小写就可以
Union

 minus select * from table where rownum<=20
minus
select *from table where rownum<=10

----------------------------------------------------------------------------------------------------------------------------------

break on deptno skip 1
break on ""
select deptno,ename,sal,sum(sal) over (order by deptno) from emp order by deptno;
select deptno,ename,sal,sum(sal) over (partition by deptno order by deptno desc) from emp order by deptno;
    
    
      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：    
   over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
   over（partition by deptno）按照部门分区
2：
  over（order by salary range between 5 preceding and 5 following）
   每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
 select rownum,month,sell from (select month,sell from sale group by month,sell) where rownum<13;

select rownum,month,sell from (select sell,month from sale group by sell,month) where rownum<13;

select * from (select rownum row_id ,month,sell 
  2  from (select month,sell from sale group by month,sell)) 
  3  where row_id between 5 and 9;

set linesize 1000

set echo off

set term off

set heading off

set pagesize 0

set trimspool on

set feedback off

spool d:\hu.txt
select name from s_prod_int;
spool off