Oracle使用技巧之case子句的用途

1、case 子句的用用途語法： caase whenn 條件1 thenn 值1 wheen條件22 theen 值2。when條條件n tthen 值nelse 值elsseendd case子子句相當(dāng)于于一個(gè)具有有多個(gè)選擇擇的語句，在在多個(gè)不同同的條件中中，選擇其其中之一，與與javaa語言的ccase語語句類似。例：如果表表chinnese的的字段bhh為nulll,則返返回0，如如果字段bbh為1,則返回999，字段段bh為其其它值，原原樣返回。selecct ccase whenn bh is nulll thenn 0 whenn bh=1 thenn 99 elsse bhh ee

2、nd bbhs, xhh,codde frrom cchineese 一、利用CCASE 語句更新新列值執(zhí)行本語句句將性別為為男的伙食食補(bǔ)助修改改為1200，女修改改為1000UPDATTE TBB_EMPP2SET 伙伙食補(bǔ)助=CASEE WHEEN 性別別=男 theen 1220 WHEEN 性別別=女 theen 1000END當(dāng)列cj的的值為744時(shí)，修改改其值為880, 為97時(shí)時(shí)，修改其其值為1000,其它它值不變updatte tbb set ccj=(casse cjj whenn 74 thenn 80when 97 thenn 100else cj eend);當(dāng)列kc的

3、的值為語文時(shí)，修改改列cj=cj+66, 為物理時(shí)，修改改其值為ccj+4,其它值不不變updatte tbb set ccj=(casse whenn kc=語文 thenn cj+6when kc=物理 thenn cj+4else cj eend);/利用CASSE 語句句進(jìn)行更新新列值執(zhí)行本語句句將性別為為男的伙食食補(bǔ)助修改改為1200，女修改改為1000UPDATTE TBB_EMPP2SET 伙伙食補(bǔ)助=CASEE WHEEN 性別別=男 theen 1220 WHEEN 性別別=女 theen 1000END當(dāng)列cj的的值為744時(shí)，修改改其值為880, 為97時(shí)時(shí)，修改其其值為

4、1000,其它它值不變updatte tbb set ccj=(casse cjj whenn 74 thenn 80when 97 thenn 100else cj eend);當(dāng)列kc的的值為語文時(shí)，修改改列cj=cj+66, 為物理時(shí)，修改改其值為ccj+4,其它值不不變updatte tbb set ccj=(casse whenn kc=語文 thenn cj+6when kc=物理 thenn cj+4else cj eend);case語語句在SQQL中的使使用方法 Case具具有兩種格格式。簡單單Casee函數(shù)和CCase搜搜索函數(shù)。 1.簡單CCase函函數(shù)CASE sex

5、WHENN 1 THEEN 男男 WHENN 2 THEEN 女女ELSE 其他 ENDD2.搜索ccase函函數(shù)CASE WHENN sexx = 1 TTHEN 男 WHENN sexx = 2 TTHEN 女ELSE 其他 ENDD這兩種方式式，可以實(shí)實(shí)現(xiàn)相同的的功能。簡簡單Casse函數(shù)的的寫法相對(duì)對(duì)比較簡潔潔，但是和和Casee搜索函數(shù)數(shù)相比，功功能方面會(huì)會(huì)有些限制制，比如寫寫判斷式。 還有一個(gè)需需要注意的的問題，CCase函函數(shù)只返回回第一個(gè)符符合條件的的值，剩下下的Casse部分將將會(huì)被自動(dòng)動(dòng)忽略。 -比如說說，下面這這段SQLL，你永遠(yuǎn)遠(yuǎn)無法得到到“第二類”這個(gè)結(jié)果果CASE

6、WHENN coll_1 IN ( a, b) THHEN 第一類 WHENN coll_1 IIN (a) THHEN 第二類ELSE其他 ENDCase使使用集錦一，已知數(shù)數(shù)據(jù)按照另另外一種方方式進(jìn)行分分組，分析析。 下面的CCase語語句用在GGroupp by 子句中有如下數(shù)據(jù)據(jù):(為了了看得更清清楚，我并并沒有使用用國家代碼碼，而是直直接用國家家名作為PPrimaary KKey) 國家（coountrry） 人人口（poopulaationn） 中國 600 美國 100 加拿大 100 英國 200 法國 300 日本 250 德國 200 墨西哥 50 印度 250 根據(jù)國家人

7、人口數(shù)據(jù)，統(tǒng)統(tǒng)計(jì)亞洲和和北美洲的的人口數(shù)量量。應(yīng)該得得到下面這這個(gè)結(jié)果。 洲 人人口 亞洲 11100 北美洲 250 其他 7700 想要解決這這個(gè)問題，你你會(huì)怎么做做？生成一一個(gè)帶有洲洲Codee的Vieew，是一一個(gè)解決方方法，但是是這樣很難難動(dòng)態(tài)的改改變統(tǒng)計(jì)的的方式。 如果使用CCase函函數(shù)，SQQL代碼如如下: SELECCT SSUM(ppopullatioon), CCASE counntry WWHEN 中國 THEEN 亞亞洲 WWHEN 印度 THEEN 亞亞洲 WWHEN 日本 THEEN 亞亞洲 WWHEN 美國 THEEN 北北美洲 WWHEN 加拿大大 TTHEN

8、 北美洲洲 WWHEN 墨西哥哥 TTHEN 北美洲洲 EELSE 其他 ENDDFROM TTablee_AGROUPP BY CASEE couuntryy WWHEN 中國 THEEN 亞亞洲 WWHEN 印度 THEEN 亞亞洲 WWHEN 日本 THEEN 亞亞洲 WWHEN 美國 THEEN 北北美洲 WWHEN 加拿大大 TTHEN 北美洲洲 WWHEN 墨西哥哥 TTHEN 北美洲洲 EELSE 其他 ENDD;同樣的，我我們也可以以用這個(gè)方方法來判斷斷工資的等等級(jí)，并統(tǒng)統(tǒng)計(jì)每一等等級(jí)的人數(shù)數(shù)。SQLL代碼如下下； SELECCT CCASE WHENN sallary 500

9、0 ANND saalaryy 6000 ANND saalaryy 8000 ANND saalaryy = 10000 THEEN 44 EELSE NULLL ENDD sallary_classs, CCOUNTT(*)FROM TTablee_AGROUPP BY CCASE WHENN sallary 5000 ANND saalaryy 6000 ANND saalaryy 8000 ANND saalaryy 10000 TTHEN 1 ELLSE 00 ENDD EELSE 1 ENND = 1 )如果單純使使用Cheeck，如如下所示 CONSTTRAINNT chheck

10、_salaary CCHECKK ( sex = 22 ANND saalaryy 11000 )女職員的條條件倒是符符合了，男男職員就無無法輸入了了。這是一一個(gè)非常經(jīng)經(jīng)典的用法法！四，根據(jù)條條件有選擇擇的UPDDATE。 下面的CCase語語句用在uupdatte子句中中，如果進(jìn)進(jìn)行兩次更更新，則可可能會(huì)產(chǎn)生生錯(cuò)誤！例，有如下下更新條件件 工資50000以上的的職員，工工資減少110% 工資在20000到44600之之間的職員員，工資增增加15% 很容易考慮慮的是選擇擇執(zhí)行兩次次UPDAATE語句句，如下所所示 -條件11UPDATTE PeersonnnelSET ssalarry = s

11、alaary * 0.99WHEREE sallary = 55000;-條件22UPDATTE PeersonnnelSET ssalarry = salaary * 1.115WHEREE sallary = 22000 AND salaary = 50000 THENN sallary * 0.9WHEN salaary = 20000 AAND ssalarry 46000THEN salaary * 1.115ELSE salaary EEND;這里要注意意一點(diǎn)，最最后一行的的ELSEE sallary是是必需的，要要是沒有這這行，不符符合這兩個(gè)個(gè)條件的人人的工資將將會(huì)被寫成成NUl

12、ll,那可就就大事不妙妙了。在CCase函函數(shù)中Ellse部分分的默認(rèn)值值是NULLL，這點(diǎn)點(diǎn)是需要注注意的地方方。 五變更主主鍵列的值值本質(zhì)上任任然是Caase語句句在upddate中中的應(yīng)用一般情況下下，要想把把兩條數(shù)據(jù)據(jù)的Priimaryy keyy,a和bb交換，需需要經(jīng)過臨臨時(shí)存儲(chǔ)，拷拷貝，讀回回?cái)?shù)據(jù)的三三個(gè)過程，要要是使用CCase函函數(shù)的話，一一切都變得得簡單多了了。 p_keyy coll_1 ccol_22 a 1 張三 b 2 李四 c3 王五 假設(shè)有如上上數(shù)據(jù)，需需要把主鍵鍵a和b相相互交換。用Casse函數(shù)來來實(shí)現(xiàn)的話話，代碼如如下 UPDATTE SoomeTaabl

13、eSET pp_keyy =CASE WHENN p_kkey = a THEEN bbWHEN p_keey = b THEEN aaELSE p_keey ENNDWHEREE p_kkey IIN (a, b);同樣的也可可以交換兩兩個(gè)Uniique key。需要注意意的是，如如果有需要要交換主鍵鍵的情況發(fā)發(fā)生，多半半是當(dāng)初對(duì)對(duì)這個(gè)表的的設(shè)計(jì)進(jìn)行行得不夠到到位，建議議檢查表的的設(shè)計(jì)是否否妥當(dāng)。 六，兩個(gè)表表數(shù)據(jù)是否否一致的檢檢查。 Casee函數(shù)中，可可以使用下下列子句：BETWEEEN AND , LIKE,IS NUULL,IN, NNOT IINEXISTTS 和NOT EXISS

14、TSCase函函數(shù)不同于于DECOODE函數(shù)數(shù)。在Caase函數(shù)數(shù)中，可以以使用BEETWEEEN,LIIKE,IIS NUULL,IIN,EXXISTSS等等。比比如說使用用IN,EEXISTTS，可以以進(jìn)行子查查詢，從而而 實(shí)現(xiàn)更更多的功能能。 下面具個(gè)例例子來說明明，有兩個(gè)個(gè)表，tbbl_A,tbl_B，兩個(gè)個(gè)表中都有有keyCCol列?，F(xiàn)在我們們對(duì)兩個(gè)表表進(jìn)行比較較，tbll_A中的的keyCCol列的的數(shù)據(jù)如果果在tbll_B的kkeyCool列的數(shù)數(shù)據(jù)中可以以找到， 返回結(jié)果果Mattchedd,如果果沒有找到到，返回結(jié)結(jié)果Unnmatcched。 要實(shí)現(xiàn)下面面這個(gè)功能能，可以使使

15、用下面兩兩條語句 1.使用IIN的時(shí)候候SELECCT keeyColl,CASE WHENN keyyCol IN ( SELEECT kkeyCool FRROM ttbl_BB ) whenn中使用in 子句句THEN MattcheddELSE Unmmatchhed END LabeelFROM tbl_A;2.使用EEXISTTS的時(shí)候候SELECCT keeyColl,CASE WHENN EXIISTS ( SEELECTT * FFROM tbl_BWHEREE tbll_A.kkeyCool = tbl_B.keeyColl )THEN MattcheddELSE Unmm

16、atchhed END LabeelFROM tbl_A;使用IN和和EXISSTS的結(jié)結(jié)果是相同同的。也可可以使用NNOT IIN和NOOT EXXISTSS，但是這這個(gè)時(shí)候要要注意NUULL的情情況。 七，在Caase函數(shù)數(shù)中使用合合計(jì)函數(shù) 假設(shè)有下面面一個(gè)表學(xué)號(hào)(sttd_idd) 課程程ID(cclasss_id) 課程名名(claass_nname) 主修fflag（mmain_classs_fllg) 100 11 經(jīng)濟(jì)學(xué)學(xué) Y 100 22 歷史學(xué)學(xué) N 200 22 歷史學(xué)學(xué) N 200 33 考古學(xué)學(xué) Y 200 44 計(jì)算機(jī)機(jī) N 300 44 計(jì)算機(jī)機(jī) N 400 55

17、化學(xué) N 500 66 數(shù)學(xué) N 有的學(xué)生選選擇了同時(shí)時(shí)修幾門課課程(1000,2000)也有有的學(xué)生只只選擇了一一門課程(300,400,500)。選修多多門課程的的學(xué)生，要要選擇一門門課程作為為主修，主主修flaag里面寫寫入 Y。只選擇一一門課程的的學(xué)生，主主修flaag為N(實(shí)際上要要是寫入YY的話，就就沒有下面面的麻煩事事了，為了了舉例子，還還請(qǐng)多多包包含)。 現(xiàn)在我們要要按照下面面兩個(gè)條件件對(duì)這個(gè)表表進(jìn)行如下下查詢 ：只選修一門門課程的學(xué)學(xué)生，返回回那門課程程的ID 選修多門課課程的學(xué)生生，返回所所選的主課課程ID 簡單的想法法就是，執(zhí)執(zhí)行兩條不不同的SQQL語句進(jìn)進(jìn)行查詢。 -條

18、件11：只選擇擇了一門課課程的學(xué)生生SELECCT sttd_idd, MAAX(cllass_id) AS mmain_classsFROM StuddentcclasssGROUPP BY std_idHAVINNG COOUNT(*) = 1;執(zhí)行結(jié)果11 STD_IID MAINN_claass- -300 4400 5500 6-條件22：選擇多多門課程的的學(xué)生SELECCT sttd_idd, cllass_id AAS maain_cclasssFROM StuddentcclasssWHEREE maiin_cllass_flg = YY ;執(zhí)行結(jié)果22 STD_IID MMA

19、IN_classs- -100 11200 33如果使用CCase函函數(shù)，我們們只要一條條SQL語語句就可以以解決問題題，具體如如下所示 SELECCT sstd_iid,CASE WHENN COUUNT(*) = 1 THHEN MMAX(cclasss_id) -只選擇一一門課程的的學(xué)生的情情況ELSE MAX(CASEE WHEEN maain_cclasss_flgg = YTHEN classs_iddELSE NULLL ENDD)END AAS maain_cclasssFROM StuddentcclasssGROUPP BY std_id;運(yùn)行結(jié)果 STD_IID MAIN

20、N_claass- -100 1200 3300 4400 5500 6通過在Caase函數(shù)數(shù)中嵌套CCase函函數(shù)，在合合計(jì)函數(shù)中中使用Caase函數(shù)數(shù)等方法，我我們可以輕輕松的解決決這個(gè)問題題。使用CCase函函數(shù)給我們們帶來了更更大的自由由度。 最后提醒一一下使用CCase函函數(shù)的新手手注意不要要犯下面的的錯(cuò)誤 CASE col_1WHEN 1 THEEN RRighttWHEN NULLL THHEN WronngEND在這個(gè)語句句中Wheen Nuull這一一行總是返返回unkknownn，所以永永遠(yuǎn)不會(huì)出出現(xiàn)Wroong的情情況。因?yàn)闉檫@句可以以替換成WWHEN col_1 = N

21、ULLL，這是一一個(gè)錯(cuò)誤的的用法，這這個(gè)時(shí)候我我們應(yīng)該選選擇用WHHEN ccol_11 IS NULLL。正確的的做法是：CASE col_1WHEN 1 THENN RiightWHEN IS NUULL THENN WrrongENDcoaleesce函函數(shù)語法：cooalessce(paraam1,pparamm2)當(dāng)?shù)谝粋€(gè)參參數(shù)parram1(通常為字字段)為nulll時(shí)，返返回第二個(gè)個(gè)參數(shù)paaram22的值，否否則，返回回第一個(gè)參參數(shù)的值.這個(gè)函數(shù)數(shù)與NVLL函數(shù)功能能相同。例： seelectt coallescee(bh,0) bhhs frrom cchineese sel

22、eect nnvl(bbh,0) bhhs frrom cchineese從表中隨機(jī)機(jī)返回n條條記錄實(shí)現(xiàn)此功能能需要使用用函數(shù)dbbms_rrandoom.vaalue()作為oorderr by子子句的參數(shù)數(shù)，這樣查詢將將依賴于函函數(shù)的結(jié)果果進(jìn)行排序序。利用這這個(gè)函數(shù)可可以實(shí)現(xiàn)隨隨機(jī)出題例1：隨機(jī)機(jī)產(chǎn)生1000條歇后后語記錄Selecct * from (selecct xhh,conntentt,keyys frrom xxhy oorderr by dbbms_rrandoom.vaalue()Wheree rownnum=100;例2：利用用函數(shù)dbbms_rrandoom.vaalu

23、e()產(chǎn)生1100個(gè)隨隨機(jī)數(shù)Oraclle 100g方法selecct dbbms_rrandoom.vaalue() frrom ddual connnect by rownnum=100Oraclle 9ii方法selecct dbbms_rrandoom.vaalue() frrom all_objeects rownnum=100根據(jù)學(xué)生成成績排名次次要求：要求求輸出前nn名學(xué)生，同同一成績名名次相同。原理：利用用densse_raank()這個(gè)分析析函數(shù)進(jìn)行行名次排列列主要數(shù)據(jù)如如下Xmsccorec72x83b83d65h96n57y90m99v1000輸出前5名名Xmsccore

24、 mcn571d652c723x834b834y905selecct * fromm( sselecct xmm,scoore,ddensee_rannk()oover(ordeer by sccore) mc fromm stuudenttscorre t)wherre mcc=5oraclle使用mergge更新或插插入數(shù)據(jù)使用merrge比傳傳統(tǒng)的先判判斷再選擇擇插入或更更新快很多多。1）主主要功能提提供有條件件地更新和和插入數(shù)據(jù)據(jù)到數(shù)據(jù)庫庫表中如果果該行存在在，執(zhí)行一一個(gè)UPDDATE操操作，如果果是一個(gè)新新行，執(zhí)行行INSEERT操作作 避免了了分開更新新 提高性性能并易于于使用 在

25、數(shù)據(jù)據(jù)倉庫應(yīng)用用中十分有有用2）MMERGEE語句的語語法如下：MERGGE hhint INTTO sschemma . tabble t_allias USIING scheema . ttablee | vview | suubqueery t_aliaas OON ( condditioon )WWHEN MATCCHED THENN merrge_uupdatte_cllauseeWHENN NOTT MATTCHEDD THEEN meerge_inseert_cclausse;還是是看例子就就知道怎么么回事：MMERGEE INTTO coopy_eemp ccUSINNG em

26、mployyees eON (c.eemplooyee_id=ee.empployeee_idd)WHEEN MAATCHEED THHENUPPDATEE SETTc.fiirst_namee=e.ffirstt_namme,c.lastt_namme=e.lastt_namme,c.depaartmeent_iid=e.depaartmeent_iidWHEEN NOOT MAATCHEED THHENINNSERTT VALLUES(e.emmployyee_iid,e.firsst_naame,ee.lasst_naame,ee.emaail,ee.phoone_nnumbeer,e.

27、hiree_datte,e.job_id,ee.sallary,e.coommisssionn_pctt,e.mmanagger_iid,e.depaartmeetn_iid);MMERGEE INTTO coopy_eemp ccUSINNG emmployyees eON (c.eemplooyee_id=ee.empployeee_idd)WHEEN MAATCHEED THHENUPPDATEE SETTc.fiirst_namee=e.ffirstt_namme,c.lastt_namme=e.lastt_namme,c.depaartmeent_iid=e.depaartmeent

28、_iidWHEEN NOOT MAATCHEED THHENINNSERTT VALLUES(e.emmployyee_iid,e.firsst_naame,ee.lasst_naame,ee.emaail,ee.phoone_nnumbeer,e.hiree_datte,e.job_id,ee.sallary,e.coommisssionn_pctt,e.mmanagger_iid,e.depaartmeetn_iid);33）使用mmergee的注意事事項(xiàng)：創(chuàng)建建測試表：CREAATE TTABLEE MM (ID NUMBBER, NAMEE VARRCHARR2(200);CCREAT

29、TE TAABLE MN (ID NNUMBEER, NNAME VARCCHAR22(20);插入入數(shù)據(jù)INNSERTT INTTO MMM VALLUES (1, A);INSSERT INTOO MN VALUUES (1, B);執(zhí)行：MEERGE INTOO MN AUSIING MMM BOON(A.ID=BB.ID)WHENN MATTCHEDD THEENUPDDATE SET A.IDD = BB.IDWWHEN NOT MATCCHED THENNINSEERT VVALUEES(B.ID, B.NAAME);ON(AA.ID=B.IDD)報(bào)錯(cuò):無效的標(biāo)標(biāo)識(shí)符,這這個(gè)錯(cuò)誤提

30、提示有些誤誤導(dǎo)嫌疑，原因是on子句的使用的字段不能夠用于update，即Oracle不允許更新用于連接的列修改：MERGE INTO MN AUSING MM BON(A.ID=B.ID)WHEN MATCHED THENUPDATE SET A.NAME = B.NAMEWHEN NOT MATCHED THENINSERT VALUES(B.ID, B.NAME);ON(A.ID=B.ID)再插入：INSERT INTO MM VALUES (1, C);再執(zhí)行：MERGE INTO MN AUSING MM BON(A.ID=B.ID)WHEN MATCHED THENUPDATE SE

31、T A.NAME = B.NAMEWHEN NOT MATCHED THENINSERT VALUES(B.ID, B.NAME);ON(A.ID=B.ID)報(bào)錯(cuò)，原因無法在源表中獲得一組穩(wěn)定的行4）更新同一張表的數(shù)據(jù)。需要注意下細(xì)節(jié)，因?yàn)榭赡苌婕暗絬sing的數(shù)據(jù)集為null,所以要使用count()函數(shù)。MERGE INTO mn aUSING (select count(*) co from mn where mn.ID=4) bON (b.co0)-這里使用了count和，注意下，想下為什么！WHEN MATCHED THENUPDATESET a.NAME = Ewhere a.ID

32、=4WHEN NOT MATCHED THENINSERTVALUES (4, E);計(jì)算中位(間)值一、問題計(jì)算一列數(shù)數(shù)字值的中中間值（中中間值就是是一組有序序元素中間間成員的值值）。假設(shè)有如下下數(shù)據(jù)：creatte taable empIInfo( namee varrcharr2(200), sal numbber(66)inserrt innto eempInnfo vvaluees(王王宏,8800);inserrt innto eempInnfo vvaluees(留留心會(huì),11000);inserrt innto eempInnfo vvaluees(張張三,11500);ins

33、errt innto eempInnfo vvaluees(李李薇霞,30000);inserrt innto eempInnfo vvaluees(大大大村,25000);inserrt innto eempInnfo vvaluees(楊楊偉,22750);selecct * fromm emppInfoo ordder by saal王宏8000留心會(huì)11100張三15500大大村22500楊偉27750李薇霞33000中位數(shù)應(yīng)為為：20000=(11500+25000)/2二、解決方方案除了Oraacle解解決方案（用用函數(shù)計(jì)算算中間數(shù)）之之外，其他他所有解決決方案都是是以Rozzen

34、shhteinn、Abrramovvich和和Birgger在OOptimmizinng Trransaact-SSQL: Advaancedd Proogrammmingg Tecchniqques (SQLL Forrum PPresss, 19997)中中描述的方方法為基礎(chǔ)礎(chǔ)的。與傳傳統(tǒng)的自聯(lián)聯(lián)接相比，窗口函數(shù)的引入，使解決方案更為有效。1. 通用用求中位數(shù)數(shù)的方法使用自聯(lián)接接查找中間間數(shù)：其原原理是：首先進(jìn)行自自連接，獲獲得笛卡爾爾積；其次次計(jì)算各記記錄sall字段的差差并轉(zhuǎn)換為為符號(hào)函數(shù)數(shù)，即：siggn(e.sal - d.sal)以及各個(gè)個(gè)記錄saal字段是是否相同，用用下列語句

35、句實(shí)現(xiàn)：case whenn e.sal = d.sal thenn 1 ellse 00 endd；然后再再進(jìn)行分組組，并計(jì)算算組內(nèi)saal字段的的平均值。如果一列數(shù)數(shù)為奇數(shù)個(gè)個(gè)數(shù)，則這這列數(shù)必定定關(guān)于中間間一個(gè)數(shù)的的abs(sum(signn(e.sal - d.sal)成對(duì)稱稱。如果一一列數(shù)為偶偶數(shù)個(gè)數(shù)，則則這列數(shù)必必定關(guān)于中中間的兩個(gè)個(gè)數(shù)成對(duì)稱稱。selecct avg(sal) fromm ( -找找到中間位位置的saal值 sellect e.sal froom emmpInffo e, emppInfoo d grooup by e.sal havving sum(caase w

36、henn e.sal = d.sal thenn 1 ellse 00 endd )= abs(sum(signn(e.sal - d.sal)-eend aabs)t根據(jù)第一個(gè)個(gè)自聯(lián)接表表EMP計(jì)計(jì)算中間數(shù)數(shù)，而該表表返回了所所有工資的的笛卡兒積積（GROOUP BBY E.SAL會(huì)會(huì)去掉重復(fù)復(fù)值）。HHAVINNG子句使使用函數(shù)SSUM計(jì)算算E.SAAL等于DD.SALL的次數(shù)；如果這個(gè)個(gè)值大于等等于E.SSAL且大大于D.SSAL次數(shù)數(shù)，那么該該行就是中中間數(shù)。在在SELEECT列表表中加入SSUM就可可以觀察到到這種情況況：selecct avg(sal) fromm ( -找找到中間

37、位位置的saal值 sellect e.sal,counnt(e.sal) ncoount,sum(casee whenn e.sal = d.sal thenn 1 ellse 00 endd) summ0,abs(sum(signn(e.sal - d.sal) ) abss0 froom emmpInffo e, emppInfoo d grooup by e.sal havving sum(caase whenn e.sal = d.sal thenn 1 ellse 00 endd )= abs(sum(signn(e.sal - d.sal)-eend aabs)t80071611

38、00714150071222007102500712275071430007162.DB22,Oraacle 數(shù)據(jù)庫使用窗口函函數(shù)COUUNT(*) OVVER和RROW_NNUMBEER，查找找中間數(shù)：selecct avg(ssal) fromm(-通過分分析函數(shù)為為查詢添加加3列：ttotall，hallf，xhh sellect sal, counnt(*) oveer() totaal, flooor(ccountt(*) overr()/22) hallf, row_numbber() oveer(orrder by saal) xh froom emmpInffo)wherre

39、(modd(tottal,22)=0 and xxh inn(hallf,haalf+11)偶數(shù)行，取取中間兩行行的值 or (modd(tottal,22)=1 and xxh=haalf+11) 奇數(shù)行，取取中間行的的值執(zhí)行結(jié)結(jié)果：2000=(11500+25000)/23.Oraacle數(shù)數(shù)據(jù)庫使用函數(shù)MMEDIAAN（Orraclee Dattabasse 100g）或PERRCENTTILE_CONTT（Oraacle99i 及以以上版本）：-使用于于Oraccle100g 及以上版版本selecct mediian(ssal) frrom eempInnfo -使用于于Oraccl

40、e9ii 及以上上版本 seleect perccentiile_ccont(0.5) witthin grouup(ordeer by saal) fromm emppInfoo 說明：對(duì)于于Oraccle8ii Dattabasse，使用用DB2解解決方案。對(duì)于Orraclee8i DDatabbase之之前的版本本，可以采采用PosstgreeSQL/MySQQL解決方方案。4.SQLL Serrver 數(shù)據(jù)庫(sqlsserveer20005以上版版本)使用窗口函函數(shù)COUUNT(*) OVVER和RROW_NNUMBEER，可得得到中間數(shù)數(shù)：-創(chuàng)建數(shù)數(shù)據(jù)表creatte tablle

41、 emmpInffo( namee varcchar(20), sal int)-向表插插入數(shù)據(jù)inserrt intoo emppInfoo vallues(王宏,800);inserrt intoo emppInfoo vallues(留心會(huì)會(huì),11000);inserrt intoo emppInfoo vallues(張三,15000);inserrt intoo emppInfoo vallues(李薇霞霞,30000);inserrt intoo emppInfoo vallues(大大村村,25000);inserrt intoo emppInfoo vallues(楊偉,275

42、00);inserrt intoo emppInfoo vallues(華陽,22000);-查詢selecct * fromm emppInfoo ordder by saal王宏8800留心會(huì)11100張三11500華陽22200大大村22500楊偉22750李薇霞33000-獲得中中位數(shù)selecct avg(sal) fromm( sellect sal, ccountt(*) overr() tootal, ffloorr(counnt(*) overr()/2) hallf, rrow_nnumbeer() overr(ordeer by saal) xhh froom emmpI

43、nffo) twheree (xh %2=0 andd xh in(haalf,halff+1)-偶偶數(shù)行，取取中間兩行行的值 or (xh % 2=1 andd xh=halff+1) -數(shù)行行，取中間間行的值三、各種方方案總結(jié)1.DB22和SQLL Serrver 解決方案案的唯一差差別是語法法的稍許不不同：SQQL Seerverr用“%”求模，而而DB2和和Oraccle使用用MOD函函數(shù)；其余余的都相同同。2.內(nèi)聯(lián)視視圖X返回回三個(gè)不同同的計(jì)數(shù)值值，TOTTAL和hhalfTT，還用到到由ROWW_NUMMBER生生成的RNN。這些附附加列有助助于求解中中間數(shù)。檢檢驗(yàn)內(nèi)聯(lián)視視圖X的結(jié)

44、結(jié)果集，就就會(huì)看到這這些列表示示的意義：3.根據(jù)據(jù)第一個(gè)自自聯(lián)接表EEMP計(jì)算算中間數(shù)，而而該表返回回了所有工工資的笛卡卡兒積（GGROUPP BY E.SAL會(huì)會(huì)去掉重復(fù)復(fù)值）。HHAVINNG子句使使用函數(shù)SSUM計(jì)算算E.SAAL等于DD.SALL的次數(shù)；如果這個(gè)個(gè)值大于等等于E.SSAL且大大于D.SSAL次數(shù)數(shù)，那么該該行就是中中間數(shù)。在在SELEECT列表表中加入SSUM就可可以觀察到到這種情況況： -找找到中間位位置的saal值 sellect e.sal,counnt(e.sal) ncoount,sum(casee whenn e.sal = d.sal thenn 1 el

45、lse 00 endd) summ0,abs(sum(signn(e.sal - d.sal) ) abss0 froom emmpInffo e, emppInfoo d grooup by e.salOraclle在Oraccle DDatabbase 10g或OOraclle9i Dataabasee中，可以以使用Orraclee提供的函函數(shù)計(jì)算中中間數(shù)；對(duì)對(duì)于Oraacle88i Daatabaase，可可以采用DDB2解決決方案；其其他版本只只能采用通通用求中位位數(shù)的解決決方案。顯顯然可以用用MEDIIAN函數(shù)數(shù)計(jì)算中間間值，用PPERCEENTILLE_COONT函數(shù)數(shù)也可以計(jì)計(jì)算

46、中間值值就不那么么顯而易見見了。傳遞遞給PERRCENTTILE_CONTT的值0.5是一個(gè)個(gè)百分比值值。子句WWITHIIN GRROUP (ORDDER BBY SAAL)確定定PERCCENTIILE_CCONT要要搜索哪些些有序行（記記住，中間間值就是一一組已排序序值的中間間值）。返返回的值就就是搜索的的有序行中中符合給定定百分比（在在這個(gè)例子子中是0.5，因?yàn)闉槠鋬蓚€(gè)邊邊界值分別別為0和11）的值?？傊?，通用用解決方案案適用于各各種版本各各種類型的的數(shù)據(jù)庫，但但效率較低低；Oraclle 提供供了響應(yīng)的的函數(shù)，但但對(duì)版本要要求較高；Sqlsserveer和Orraclee 一樣，對(duì)對(duì)

47、版本有要要求。計(jì)算眾數(shù)的的方法一、準(zhǔn)備數(shù)數(shù)據(jù)selecct * fromm tb張三語文文74張三數(shù)學(xué)學(xué)83張三物理理93李四語文文74李四數(shù)學(xué)學(xué)84劉洋語文文81劉洋數(shù)學(xué)學(xué)82劉洋物理理86二、求眾數(shù)數(shù)的一般方方法：-首先構(gòu)構(gòu)造CTEEwith tbGrroup as (seleect xxm,counnt(*) tottal ffrom tb ggroupp by xmm)-找出具具有最大值值的所有記記錄(即眾眾數(shù))方案1：selecct * fromm tbGGrouppwheree tottal=(seleect max(totaal) fromm tbGGroupp 求各組內(nèi)內(nèi)的最大

48、值值)顯示結(jié)果：劉洋3張三3方案2：selecct * fromm (seleect xxm,totaal,densse_raank() overr(ordeer by tootal descc) mc fromm tbGGroupp)twheree mc=1顯示結(jié)果：劉洋31張三31即該組數(shù)中中有2個(gè)眾數(shù)。注意：一組數(shù)可可能沒有眾眾數(shù)！此時(shí)時(shí)CTE將將返回空集集！查詢出連續(xù)續(xù)的記錄有一個(gè)表結(jié)結(jié)構(gòu)，fpphm,kkshm,其記錄如如下：2014,00000000112014,00000000222014,00000000332014,00000000442014,00000000552014,

49、00000000772014,00000000882014,00000000992013,00000012002013,00000012112013,00000012222013,00000012442013,0000001255(第二個(gè)字字段內(nèi)可能能是連續(xù)的的數(shù)據(jù)，可可能存在斷斷點(diǎn)。)要求查詢出出連續(xù)的記記錄來。就就像下面的的這樣？2014,0000000011,00000000052014,0000000099,00000000072013,0000001200,00000012222013,0000001244,0000001225SELECCT b.fphmm, MIIN (bb.ks

50、hhm) SStartt_HM, MAXX (b.kshmm) Ennd_HMMFROM (SELECCT a.*, TTO_NUUMBERR (a.ksshm - ROWWNUM) ccFROM (SELLECT * FRROM tt ORDDER BBY fpphm, kshmm) a) bGROUPP BY b.fpphm, b.cccSELECCT b.fphmm, MIIN (bb.kshhm) SStartt_HM, MAXX (b.kshmm) Ennd_HMMFROMM (SELLECT a.*, TO_NUMBBER (a.ksshm - ROWWNUM) cc FROMM

51、(SEELECTT * FFROM t ORRDER BY ffphm, kshhm) aa) bGROUUP BYY b.ffphm, b.cccFPHM STARRT_HMM ENDD_HM- - -2013 0000001200 00000012222013 0000001244 00000012252014 0000000011 00000000052014 0000000077 0000000009/查詢所有物物品的最后后出庫時(shí)間間物品 出庫時(shí)間間 a 20005-3-4a 20005-122-3a 20066-10-8a 20007-5-1b 20006-1-1b 20006-11

52、0-2b 20007-66-24c 20006-112-2c 20007-33-8-要求結(jié)果物品 最后出出庫時(shí)間a 22007-5-1b 22007-6-244c 22007-3-8 selecct mcc,maxx(rq) froom SQQLnummber grouup byy mcselecct * fromm SQLLnumbber aas a wherre exxistss (sellect top 1 * fromm SQLLnumbber aas b wherre b.mc=aa.mc ordeer byy rq descc)selecct * fromm taable as a

53、 exxits (sellect top 1 * fromm taable as b whhere b.物品=a.物品 oorderr by 出庫時(shí)時(shí)間 ddesc)農(nóng)歷】計(jì)算算方法- (陽陽歷轉(zhuǎn)陰歷歷) 因?yàn)檗r(nóng)歷的的日期，是是由天文學(xué)學(xué)家推算出出來的，到到現(xiàn)在只有有到20449年的，以以后的有了了還可以加加入！首先先建一表，放放初始化資資料1.創(chuàng)建表表CREAATE TTABLEE SollarDaata( yyearIId innt noot nuull, ddata charr(7) not nulll, dattaIntt intt nott nulll)2.插入數(shù)數(shù)據(jù)INSSERT

54、INTOO SollarDaata SSELECCT 19900,0 x044bd8,194416 UUNIONN ALLL SELLECT 19011,0 xx04aee0,1191688UNIOON ALLL SEELECTT 19002,00 x0a5700,422352 UNIOON ALLL SEELECTT 19003,00 x0544d5,217117UNIION AALL SSELECCT 19904,0 x0dd260,538856 UUNIONN ALLL SELLECT 19055,0 xx0d9550,5556322UNIOON ALLL SEELECTT 19006,

55、00 x165554,914776 UNNION ALL SELEECT 11907,0 x0056a00,222176UUNIONN ALLL SELLECT 19088,0 xx09add0,3396322 UNIION AALL SSELECCT 19909,0 x0555d2,219970UNNION ALL SELEECT 11910,0 x004ae00,199168 UNIOON ALLL SEELECTT 19111,00 x0a5b66,422422UUNIONN ALLL SELLECT 19122,0 xx0a4d00,422192 UNIOON ALLL SEELECT

56、T 19113,00 x0d2250,538440UNIION AALL SSELECCT 19914,0 x1dd255,1199381 UNIOON ALLL SEELECTT 19115,00 x0b5540,464000UNIION AALL SSELECCT 19916,0 x0dd6a0,549444 UNNION ALL SELEECT 11917,0 x00ada22,444450UUNIONN ALLL SELLECT 19188,0 xx095bb0,3383200 UNIION AALL SSELECCT 19919,0 x144977,843343UNNION ALL

57、SELEECT 11920,0 x0049700,188800 UNIOON ALLL SEELECTT 19221,00 x0a4b00,422160UUNIONN ALLL SELLECT 19222,0 xx0b4bb5,4462611 UNIION AALL SSELECCT 19923,0 x066a50,272216UNNION ALL SELEECT 11924,0 x006d400,277968 UNIOON ALLL SEELECTT 19225,00 x1abb54,1093396UNNION ALL SELEECT 11926,0 x002b600,111104 UNIO

58、ON ALLL SEELECTT 19227,00 x095570,382556UNIION AALL SSELECCT 19928,0 x0552f2,212234 UUNIONN ALLL SELLECT 19299,0 xx049770,1188000UNIOON ALLL SEELECTT 19330,00 x065566,259558 UNNION ALL SELEECT 11931,0 x00d4a0,544332UNIION AALL SSELECCT 19932,0 x0eea50,599984 UUNIONN ALLL SELLECT 19333,0 xx06e995,228

59、3099UNIOON ALLL SEELECTT 19334,00 x05aad0,232448 UNNION ALL SELEECT 11935,0 x002b600,111104UUNIONN ALLL SELLECT 19366,0 xx186ee3,11000667 UNNION ALL SELEECT 11937,0 x0092e00,377600UUNIONN ALLL SELLECT 19388,0 xx1c8d77,11169511 UNIION AALL SSELECCT 19939,0 x0cc950,515536UNNION ALL SELEECT 11940,0 x00

60、d4a0,544332 UNNION ALL SELEECT 11941,0 x11d8a6,1209998UNNION ALL SELEECT 11942,0 x00b5500,466416 UNIOON ALLL SEELECTT 19443,00 x0566a0,221776UNIION AALL SSELECCT 19944,0 x1aa5b4,1077956 UNIOON ALLL SEELECTT 19445,00 x0255d0,96800UNIOON ALLL SEELECTT 19446,00 x0922d0,375884 UNNION ALL SELEECT 11947,0