Oracle 查詢重複記錄,以及簡單的sql應用。

悠悠隱於市發表於2011-02-28
OracleSQL
--建立和刪除表;
drop table student;
create table student(id number(30),name varchar2(50),sex varchar2(100))

--建立約束; 為了能插入重複記錄,此表沒有建立月主鍵約束 或 唯一鍵約束;
--alter table student add constraints pk_student primary key(id);

--插入資料;
insert into student values(1,'A','BOY');
insert into student values(1,'A','BOY');
insert into student values(2,'B','Girl');
insert into student values(3,'C','Girl');
insert into student values(4,'D','Girl');
commit;

--查詢所有student表資料;
select rowid,id,name,sex from student;

--查詢重複記錄;(方法一)
select id,name,sex from student where id in (select id from student group by id having count(*) > 1);

--row_number()分析函式;(方法二)
select * from student where id in (select id from (select id ,row_number() over(partition by id order by rowid) m from student) where m <> 1);

--使用rowid (方法三);
select * from student a where a.rowid > (select min(b.rowid) from student b where b.id = a.id);
--分頁查詢;(方法一)
select * from(select a.*,row_number() over(order by id desc) rk from student a ) where rk<=3 and rk>=1;

--分頁查詢;(方法二)
select * from (select student.*,rownum rn from student where rownum <=3 order by id)  where rn>=1;

 

--case  when  then ,查詢報表;
select emp.empno,emp.ename,emp.job,emp.deptno,emp.sal,
       case deptno when 10  then'部門-10' 
                   when 20 then '部門-20'
                   when 30 then '部門-30'
       else 
       '其他部門' end
 from emp;
 
 --交叉報表查詢;
 select ename,sal,
 case deptno when 10 then '會計部' 
             when 20 then '研究部' 
             when 30 then '銷售部' 
             else '其他部門' end
             部門 
 from scott.emp ;
 
 --交叉報表查詢;
select empno,ename,sal,job,case when sal>=0 and sal<=1000 then '一級工資'
            when sal>=1001 and sal <= 2000 then  '二級工資'
            when sal>=2001 and sal <=3000 then   '三級工資'
            when sal>=3001 and sal <= 4000 then  '四級工資'
            when sal>= 4001 and sal <= 5000 then '五級工資'
            else 
            '其他工資'
            end
            工資等級
from emp;

--利用sum 和 case..when. then .end.行轉列的使用。
select ename as 員工姓名,sum(case JOB when 'CLERK' then sal end )接待員,
              sum(case JOB when 'SALESMAN' then sal end) 推銷員,
              sum(case JOB when 'MANAGER' then sal end)管理員,
              sum(case JOB when 'ANALYST' then sal end)分析家,
              sum(case JOB when 'PRESIDENT' then sal end)董事長
         from emp group by ename order by ename;

 

--利用sum 聚合函式與 decode 函式做一個簡單查詢， decode(輸入值,'要判斷的值'，匹配後的結果，如果沒匹配成功，則輸出預設值) ；
select ename 員工姓名,sum(decode(JOB,'CLERK',SAL,0)) 接待員,
             sum(decode(JOB,'SALESMAN',SAL,0)) 推銷員,
             sum(decode(JOB,'MANAGER',SAL,0)) 管理員,
             sum(decode(JOB,'ANALYST',SAL,0))分析家,
             sum(decode(JOB,'PRESIDENT',SAL,0))董事長
         from emp group by ename order by ename;

 

 

 

--自連線查詢;
             select a.empno,a.mgr,a.ename,a.sal,b.empno,b.ename,b.sal from scott.emp a,scott.emp b where a.mgr=b.empno ;

             --做外連線；
             select a.ename,a.sal,b.ename from scott.emp a,scott.emp b where a.mgr=b.empno(+);
             
             --自連線查詢；
             select a.empno,a.ename,a.sal,b.empno,b.ename,b.sal from emp a left join emp b on(a.mgr = b.empno) order by a.empno;
                   select * from emp order by empno;
             
             
             select emp.*,(select deptno from dept where LOC = 'NEW YORK')新增的列 from emp;
             
            --select sal from emp where emp.deptno = 30 這條語句查詢出的結果，1600,1250,1250,2850,1500,950。
            --然後，員工的工資 必須大於 子查詢查詢出來的資料。 
            select emp.* from emp where emp.sal > all(select sal from emp where emp.deptno = 30);
             
            --結果如下:
            1	7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
            2	7788	SCOTT	ANALYST	7566	1987-3-19	3000.00		20
            3	7839	KING	PRESIDENT		1981-11-17	5000.00		10
            4	7902	FORD	ANALYST	7566	1981-12-3	3000.00		20

 

 --員工工資，必須大於子查詢裡面，任何一個值，就可以。這就是any的用法；
            select emp.* from emp where emp.sal > any(select sal from emp e where e.deptno = 30);

 

 

--使用union，去除重複行，且排序。
select emp.empno,emp.ename,emp.sal from emp where emp.sal >= 1500 union 
select emp.empno,emp.ename,emp.sal from emp where emp.sal <=3000;

--使用union all直接兩個結果集合並,不排序.
select ename,sal,deptno from scott.emp where deptno>10 union all select ename,sal,deptno from scott.emp where deptno<30 ;

 

相關文章