Oracle集合操作函數(shù).doc

OracleMinus關(guān)鍵字/創(chuàng)建表1create table test1(name varchar(10),sex varchar(10),age int);insert into test1 values(luxin,female,25);insert into test1 values(tom,female,26);insert into test1 values(mary1,male,27);insert into test1 values(money,male,27);insert into test1 values(tony,male,28);insert into test1 values(tony1,male,19);/創(chuàng)建表2create table test2(name varchar(10),sex varchar(10),age int);insert into test2 values(luxin,female,25);insert into test2 values(tom,female,26);insert into test2 values(mary2,male,27);insert into test2 values(money,male,27);insert into test2 values(tony,male,28);insert into test2 values(tony2,male,19);-select * from test1 minus select * from test2;結(jié)果：NAME SEX AGE- - -mary1 male 27tony1 male 19-select * from test2 minus select * from test1;結(jié)果:NAME SEX AGE- - -mary2 male 27tony2 male 19結(jié)論：Minus返回的總是左邊表中的數(shù)據(jù)，它返回的是差集。用表1-表2中的數(shù)據(jù)，如果相同，則去掉，否則返回表1中的數(shù)據(jù)。=今天在看數(shù)據(jù)集合時(shí)偶然看到了SQL 有MINUS，可以達(dá)到相同的目的具體如下：SQL中的MINUS關(guān)鍵字SQL中有一個(gè)MINUS關(guān)鍵字，它運(yùn)用在兩個(gè)SQL語(yǔ)句上，它先找出第一條SQL語(yǔ)句所產(chǎn)生的結(jié)果，然后看這些結(jié)果有沒(méi)有在第二個(gè)SQL語(yǔ)句的結(jié)果中。如果有的話，那這一筆記錄就被去除，而不會(huì)在最后的結(jié)果中出現(xiàn)。如果第二個(gè)SQL語(yǔ)句所產(chǎn)生的結(jié)果并沒(méi)有存在于第一個(gè)SQL語(yǔ)句所產(chǎn)生的結(jié)果內(nèi)，那這筆資料就被拋棄，其語(yǔ)法如下：SQL Segment 1MINUSSQL Segment 2其實(shí)簡(jiǎn)單的一句話就是找出第一條SQL查詢的不在第二條SQL語(yǔ)句查詢結(jié)果中的那些記錄，并且要注意最后返回的結(jié)果集中，不同的記錄只會(huì)被列出一次！下面是我做的實(shí)驗(yàn)，很明顯能夠看出MINUS的效率，made_order共23萬(wàn)筆記錄，charge_detail共17萬(wàn)筆記錄性能比較:SELECT order_id FROM made_orderMINUSSELECT order_id FROM charge_detail1.14 secSELECT a.order_id FROM made_order aWHERE NOT exists ( SELECT 1 FROM charge_detail WHERE order_id = a.order_id )18.19 secSELECT order_id FROM made_orderWHERE order_id NOT in ( SELECT order_id FROM charge_detail )20.05 secOracleUnion、Union All關(guān)鍵字眾所周知的幾個(gè)結(jié)果集集合操作命令，今天詳細(xì)地測(cè)試了一下，發(fā)現(xiàn)一些問(wèn)題，記錄備考。假設(shè)我們有一個(gè)表Student，包括以下字段與數(shù)據(jù)：drop table student; create table student(id int primary key,name nvarchar2(50) not null,score number not null); insert into student values(1,Aaron,78);insert into student values(2,Bill,76);insert into student values(3,Cindy,89);insert into student values(4,Damon,90);insert into student values(5,Ella,73);insert into student values(6,Frado,61);insert into student values(7,Gill,99);insert into student values(8,Hellen,56);insert into student values(9,Ivan,93);insert into student values(10,Jay,90); commit; Union和Union All的區(qū)別。select *from studentwhere id 2 and id 2 and id 6 union select *from studentwhere id 2 and id 6 union all select *from studentwhere id 2 and id 6 union select score,id,namefrom studentwhere id 2 union select score,id,namefrom studentwhere id 2 and id 7 union select score,id,namefrom studentwhere id 8order by id desc order by子句必須寫(xiě)在最后一個(gè)結(jié)果集里，并且其排序規(guī)則將改變操作后的排序結(jié)果。對(duì)于Union、Union All、Intersect、Minus都有效。注意：1，Union可以對(duì)字段名不同但數(shù)據(jù)類型相同的結(jié)果集進(jìn)行合并；2，如果字段名不同的結(jié)果集進(jìn)行Union，那么對(duì)此字段的Order by子句將失效。=Intersect和Minus的操作和Union基本一致，這里一起總結(jié)一下：Union，對(duì)兩個(gè)結(jié)果集進(jìn)行并集操作，不包括重復(fù)行，同時(shí)進(jìn)行默認(rèn)規(guī)則的排序；Un