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

1、case 子句的用途語法： case wheen 條件1 then 值1 whhen條件22 thenn 值2。when條件nn thenn 值nelse 值eelseennd case子句相相當(dāng)于一個(gè)具具有多個(gè)選擇擇的語句，在在多個(gè)不同的的條件中，選選擇其中之一一，與javva語言的ccase語句句類似。例：如果表chhinesee的字段bhh為nulll,則返回00，如果字段段bh為1,則返回999，字段bhh為其它值，原原樣返回。select casse wheen bh is null then 0 wheen bh=1 then 99 ellse bhh endd bhs, xh,c

2、ode frrom chhinesee 一、利用CASSE 語句更更新列值執(zhí)行本語句將性性別為男的伙伙食補(bǔ)助修改改為120，女女修改為1000UPDATE TB_EMMP2SET 伙食補(bǔ)補(bǔ)助=CASSE WHEEN 性別=男 tthen 1120 WHHEN 性別別=女 then 100END當(dāng)列cj的值為為74時(shí)，修修改其值為880, 為97時(shí)，修修改其值為1100,其它它值不變update tb set cj=(casee cj when 74 then 80when 977 then 100else cjj end);當(dāng)列kc的值為為語文時(shí)，修改列列cj=cjj+6, 為物理時(shí)，修改其其

3、值為cj+4,其它值值不變update tb set cj=(casee when kkc=語文 then ccj+6when kcc=物理 then ccj+4else cjj end);/利用CASE 語句進(jìn)行更更新列值執(zhí)行本語句將性性別為男的伙伙食補(bǔ)助修改改為120，女女修改為1000UPDATE TB_EMMP2SET 伙食補(bǔ)補(bǔ)助=CASSE WHEEN 性別=男 tthen 1120 WHHEN 性別別=女 then 100END當(dāng)列cj的值為為74時(shí)，修修改其值為880, 為97時(shí)，修修改其值為1100,其它它值不變update tb set cj=(casee cj when 7

4、4 then 80when 977 then 100else cjj end);當(dāng)列kc的值為為語文時(shí)，修改列列cj=cjj+6, 為物理時(shí)，修改其其值為cj+4,其它值值不變update tb set cj=(casee when kkc=語文 then ccj+6when kcc=物理 then ccj+4else cjj end);case語句在在SQL中的的使用方法 Case具有兩兩種格式。簡(jiǎn)簡(jiǎn)單Casee函數(shù)和Caase搜索函函數(shù)。 1.簡(jiǎn)單Casse函數(shù)CASE seex WHEEN 1 THENN 男 WHEEN 2 THENN 女ELSE 其其他 ENND2.搜索casse函數(shù)

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

6、他他 ENDDCase使用集集錦一，已知數(shù)據(jù)按按照另外一種種方式進(jìn)行分分組，分析。 下面的Casse語句用在在Groupp by 子子句中有如下數(shù)據(jù):(為了看得更更清楚，我并并沒有使用國(guó)國(guó)家代碼，而而是直接用國(guó)國(guó)家名作為PPrimarry Keyy) 國(guó)家（counntry） 人口（poopulattion） 中國(guó) 6600 美國(guó) 1100 加拿大 100 英國(guó) 2200 法國(guó) 3300 日本 2250 德國(guó) 2200 墨西哥 50 印度 2250 根據(jù)國(guó)家人口數(shù)數(shù)據(jù)，統(tǒng)計(jì)亞亞洲和北美洲洲的人口數(shù)量量。應(yīng)該得到到下面這個(gè)結(jié)結(jié)果。 洲 人口 亞洲 11000 北美洲 2550 其他 7000 想

7、要解決這個(gè)問問題，你會(huì)怎怎么做？生成成一個(gè)帶有洲洲Code的的View，是是一個(gè)解決方方法，但是這這樣很難動(dòng)態(tài)態(tài)的改變統(tǒng)計(jì)計(jì)的方式。 如果使用Casse函數(shù)，SSQL代碼如如下: SELECT SUM(popullationn), CASEE counntry WWHEN 中國(guó) THHEN 亞亞洲 WWHEN 印度 THHEN 亞亞洲 WWHEN 日本 THHEN 亞亞洲 WWHEN 美國(guó) THHEN 北北美洲 WWHEN 加拿大 THENN 北美洲洲 WWHEN 墨西哥 THENN 北美洲洲 ELSEE 其他 ENDFROM Tablle_AGROUP BBY CASSE couuntry

8、WWHEN 中國(guó) THHEN 亞亞洲 WWHEN 印度 THHEN 亞亞洲 WWHEN 日本 THHEN 亞亞洲 WWHEN 美國(guó) THHEN 北北美洲 WWHEN 加拿大 THENN 北美洲洲 WWHEN 墨西哥 THENN 北美洲洲 ELSEE 其他 END;同樣的，我們也也可以用這個(gè)個(gè)方法來判斷斷工資的等級(jí)級(jí)，并統(tǒng)計(jì)每每一等級(jí)的人人數(shù)。SQLL代碼如下； SELECT CASEE WHENN salaary 500 AAND saalary 600 AAND saalary 800 AAND saalary = 10000 THHEN 44 ELSEE NULLL END salarry

9、_claass, COUNNT(*)FROM Tablle_AGROUP BBY CASEE WHENN salaary 500 AAND saalary 600 AAND saalary 800 AAND saalary 1000 THEEN 1 EELSE 00 END ELSE 1 ENDD = 1 )如果單純使用CCheck，如如下所示 CONSTRAAINT ccheck_salarry CHEECK ( sex = 2 AND salarry 11000 )女職員的條件倒倒是符合了，男男職員就無法法輸入了。這這是一個(gè)非常常經(jīng)典的用法法！四，根據(jù)條件有有選擇的UPPDATE。 下面的

10、Casse語句用在在updatte子句中，如如果進(jìn)行兩次次更新，則可可能會(huì)產(chǎn)生錯(cuò)錯(cuò)誤！例，有如下更新新條件 工資5000以以上的職員，工工資減少100% 工資在20000到46000之間的職員員，工資增加加15% 很容易考慮的是是選擇執(zhí)行兩兩次UPDAATE語句，如如下所示 -條件1UPDATE PersoonnelSET sallary = salaary * 0.9WHERE ssalaryy = 55000;-條件2UPDATE PersoonnelSET sallary = salaary * 1.15WHERE ssalaryy = 22000 AAND saalary = 5000

11、0 THENN salaary * 0.9WHEN saalary = 20000 ANND sallary 46000THEN saalary * 1.115ELSE saalary END;這里要注意一點(diǎn)點(diǎn)，最后一行行的ELSEE salaary是必需需的，要是沒沒有這行，不不符合這兩個(gè)個(gè)條件的人的的工資將會(huì)被被寫成NUlll,那可就就大事不妙了了。在Casse函數(shù)中EElse部分分的默認(rèn)值是是NULL，這這點(diǎn)是需要注注意的地方。 五變更主鍵列列的值本質(zhì)上任然是是Case語語句在upddate中的的應(yīng)用一般情況下，要要想把兩條數(shù)數(shù)據(jù)的Priimary key,aa和b交換，需需要經(jīng)過臨時(shí)時(shí)

12、存儲(chǔ)，拷貝貝，讀回?cái)?shù)據(jù)據(jù)的三個(gè)過程程，要是使用用Case函函數(shù)的話，一一切都變得簡(jiǎn)簡(jiǎn)單多了。 p_key ccol_1 col_22 a 1 張張三 b 2 李李四 c3 王五五 假設(shè)有如上數(shù)據(jù)據(jù)，需要把主主鍵a和b相相互交換。用用Case函函數(shù)來實(shí)現(xiàn)的的話，代碼如如下 UPDATE SomeTTableSET p_kkey =CASE WHHEN p_key = a THENN bWHEN p_key = b THENN aELSE p_key EENDWHERE pp_key IN (a, b);同樣的也可以交交換兩個(gè)Unnique key。需需要注意的是是，如果有需需要交換主鍵鍵的情況發(fā)

13、生生，多半是當(dāng)當(dāng)初對(duì)這個(gè)表表的設(shè)計(jì)進(jìn)行行得不夠到位位，建議檢查查表的設(shè)計(jì)是是否妥當(dāng)。 六，兩個(gè)表數(shù)據(jù)據(jù)是否一致的的檢查。 Case函數(shù)數(shù)中，可以使使用下列子句：BETWEENN ANDD , LIKE,IS NULLL,IN, NOTT INEXISTS 和NOT EXISTTSCase函數(shù)不不同于DECCODE函數(shù)數(shù)。在Casse函數(shù)中，可可以使用BEETWEENN,LIKEE,IS NNULL,IIN,EXIISTS等等等。比如說使使用IN,EEXISTSS，可以進(jìn)行行子查詢，從從而 實(shí)現(xiàn)更更多的功能。 下面具個(gè)例子來來說明，有兩兩個(gè)表，tbbl_A,ttbl_B，兩兩個(gè)表中都有有keyC

14、ool列?，F(xiàn)在在我們對(duì)兩個(gè)個(gè)表進(jìn)行比較較，tbl_A中的keeyCol列列的數(shù)據(jù)如果果在tbl_B的keyyCol列的的數(shù)據(jù)中可以以找到， 返返回結(jié)果MMatcheed,如果果沒有找到，返返回結(jié)果UUnmatcched。 要實(shí)現(xiàn)下面這個(gè)個(gè)功能，可以以使用下面兩兩條語句 1.使用IN的的時(shí)候SELECT keyCool,CASE WHHEN keeyCol IN ( SSELECTT keyCCol FRROM tbbl_B ) when中中使用in 子句THEN MMatcheedELSE UUnmatcched END LLabelFROM tbbl_A;2.使用EXIISTS的時(shí)時(shí)候SEL

15、ECT keyCool,CASE WHHEN EXXISTS ( SELLECT * FROMM tbl_BWHERE ttbl_A.keyCool = ttbl_B.keyCool )THEN MMatcheedELSE UUnmatcched END LLabelFROM tbbl_A;使用IN和EXXISTS的的結(jié)果是相同同的。也可以以使用NOTT IN和NNOT EXXISTS，但但是這個(gè)時(shí)候候要注意NUULL的情況況。 七，在Casee函數(shù)中使用用合計(jì)函數(shù) 假設(shè)有下面一個(gè)個(gè)表學(xué)號(hào)(std_id) 課課程ID(cclass_id) 課課程名(cllass_nname) 主修flaag（

16、maiin_claass_fllg) 100 1 經(jīng)經(jīng)濟(jì)學(xué) Y 100 2 歷歷史學(xué) N 200 2 歷歷史學(xué) N 200 3 考考古學(xué) Y 200 4 計(jì)計(jì)算機(jī) N 300 4 計(jì)計(jì)算機(jī) N 400 5 化化學(xué) N 500 6 數(shù)數(shù)學(xué) N 有的學(xué)生選擇了了同時(shí)修幾門門課程(1000,2000)也有的學(xué)學(xué)生只選擇了了一門課程(300,4400,5000)。選修修多門課程的的學(xué)生，要選選擇一門課程程作為主修，主主修flagg里面寫入 Y。只選擇擇一門課程的的學(xué)生，主修修flag為為N(實(shí)際上上要是寫入YY的話，就沒沒有下面的麻麻煩事了，為為了舉例子，還還請(qǐng)多多包含含)。 現(xiàn)在我們要按照照下面兩個(gè)

17、條條件對(duì)這個(gè)表表進(jìn)行如下查查詢 ：只選修一門課程程的學(xué)生，返返回那門課程程的ID 選修多門課程的的學(xué)生，返回回所選的主課課程ID 簡(jiǎn)單的想法就是是，執(zhí)行兩條條不同的SQQL語句進(jìn)行行查詢。 -條件1：只只選擇了一門門課程的學(xué)生生SELECT std_iid, MAAX(claass_idd) AS main_classsFROM SttudenttclasssGROUP BBY stdd_idHAVING COUNTT(*) = 1;執(zhí)行結(jié)果1 STD_ID MAIIN_claass- -300 4400 5500 6-條件2：選選擇多門課程程的學(xué)生SELECT std_iid, cllass

18、_iid AS main_classsFROM SttudenttclasssWHERE mmain_cclass_flg = Y ;執(zhí)行結(jié)果2 STD_ID MAINN_classs- -100 1200 3如果使用Casse函數(shù)，我我們只要一條條SQL語句句就可以解決決問題，具體體如下所示 SELECT std_id,CASE WHHEN COOUNT(*) = 11 THENN MAX(classs_id) -只選選擇一門課程程的學(xué)生的情情況ELSE MAAX(CASE WHEN main_classs_flg = YTHEN cllass_iidELSE NUULL ENND)END

19、AS main_classsFROM SttudenttclasssGROUP BBY stdd_id;運(yùn)行結(jié)果 STD_ID MAIIN_claass- -100 1200 3300 4400 5500 6通過在Casee函數(shù)中嵌套套Case函函數(shù)，在合計(jì)計(jì)函數(shù)中使用用Case函函數(shù)等方法，我我們可以輕松松的解決這個(gè)個(gè)問題。使用用Case函函數(shù)給我們帶帶來了更大的的自由度。 最后提醒一下使使用Casee函數(shù)的新手手注意不要犯犯下面的錯(cuò)誤誤 CASE cool_1WHEN 1 THEEN RiightWHEN NUULL TTHEN WronggEND在這個(gè)語句中WWhen NNull這一一行

20、總是返回回unknoown，所以以永遠(yuǎn)不會(huì)出出現(xiàn)Wronng的情況。因因?yàn)檫@句可以以替換成WHHEN cool_1 = NULLL，這是一個(gè)個(gè)錯(cuò)誤的用法法，這個(gè)時(shí)候候我們應(yīng)該選選擇用WHEEN coll_1 ISS NULLL。正確的做做法是：CASE cool_1WHEN 11 THEEN RiightWHEN IIS NULLL THHEN WWrongENDcoalescce函數(shù)語法：coallesce(paramm1,parram2)當(dāng)?shù)谝粋€(gè)參數(shù)pparam11(通常為字字段)為nulll時(shí)，返回第第二個(gè)參數(shù)pparam22的值，否則則，返回第一一個(gè)參數(shù)的值值.這個(gè)函數(shù)數(shù)與NVL函函數(shù)

21、功能相同同。例： seleect coaleesce(bbh,0) bhss fromm chinnese selecct nvll(bh,00) bhss fromm chinnese從表中隨機(jī)返回回n條記錄實(shí)現(xiàn)此功能需要要使用函數(shù)ddbms_rrandomm.valuue()作為orrder bby子句的參參數(shù)，這樣查詢將依賴賴于函數(shù)的結(jié)結(jié)果進(jìn)行排序序。利用這個(gè)個(gè)函數(shù)可以實(shí)實(shí)現(xiàn)隨機(jī)出題題例1：隨機(jī)產(chǎn)生生100條歇歇后語記錄Select * from (select xh,coontentt,keyss fromm xhy orderr by dbmms_ranndom.vvalue()Wh

22、ere rrownumm=100;例2：利用函數(shù)數(shù)dbms_randoom.vallue()產(chǎn)生1000個(gè)隨機(jī)數(shù)數(shù)Oracle 10g方法select dbms_randoom.vallue() from ddual cconnecct by rownuum=100Oracle 9i方法select dbms_randoom.vallue() from all_oobjectts rownuum=100根據(jù)學(xué)生成績(jī)排排名次要求：要求輸出出前n名學(xué)生生，同一成績(jī)績(jī)名次相同。原理：利用deense_rrank()這個(gè)分析函函數(shù)進(jìn)行名次次排列主要數(shù)據(jù)如下Xmscorrec72x83b83d65h96

23、n57y90m99v100輸出前5名Xmscorre mcn571d652c723x834b834y905select * froom( sellect xxm,scoore,deense_rrank()over(orderr by scoore) mmc froom stuudentsscore t)wheree mc=5oracle使使用mergee更新或插入入數(shù)據(jù)使用mergee比傳統(tǒng)的先先判斷再選擇擇插入或更新新快很多。11）主要功能能提供有條件件地更新和插插入數(shù)據(jù)到數(shù)數(shù)據(jù)庫(kù)表中如如果該行存在在，執(zhí)行一個(gè)個(gè)UPDATTE操作，如如果是一個(gè)新新行，執(zhí)行IINSERTT操作 避免了分分開更

24、新 提高性能能并易于使用用 在數(shù)據(jù)倉(cāng)倉(cāng)庫(kù)應(yīng)用中十十分有用2）MMERGE語語句的語法如如下：MERRGE hhint INTO scheema . tablle t_aliass USIING sschemaa . tablee | viiew | subquuery t_aalias ON ( condditionn )WHEEN MATTCHED THEN mergee_updaate_cllauseWWHEN NNOT MAATCHEDD THENN mergge_inssert_cclausee;還是看例例子就知道怎怎么回事：MMERGE INTO copy_emp ccUSINGG

25、emplloyeess eON (c.emmployeee_id=e.empployeee_id)WWHEN MMATCHEED THEENUPDAATE SEETc.fiirst_nname=ee.firsst_namme,c.llast_nname=ee.lastt_namee,c.deepartmment_iid=e.ddeparttment_idWHEEN NOTT MATCCHED TTHENINNSERT VALUEES(e.eemployyee_idd,e.fiirst_nname,ee.lastt_namee,e.emmail,ee.phonne_nummber,ee.hire

26、e_datee,e.joob_id,e.sallary,mmissioon_pctt,e.maanagerr_id,ee.depaartmettn_id);MERGGE INTTO coppy_empp cUSIING emmployeees eOON (c.emplooyee_iid=e.eemployyee_idd)WHENN MATCCHED TTHENUPPDATE SETc.firstt_namee=e.fiirst_nname,cc.lastt_namee=e.laast_naame,c.deparrtmentt_id=ee.depaartmennt_idWWHEN NNOT MA

27、ATCHEDD THENNINSERRT VALLUES(ee.emplloyee_id,e.firstt_namee,e.laast_naame,e.emaill,e.phhone_nnumberr,e.hiire_daate,e.job_iid,e.ssalaryy,e.coommisssion_ppct,e.managger_idd,e.deepartmmetn_iid);3）使使用mergge的注意事事項(xiàng)：創(chuàng)建測(cè)測(cè)試表：CRREATE TABLEE MM (ID NUUMBER, NAMEE VARCCHAR2(20);CREATTE TABBLE MNN (ID NUMBEER, N

28、AAME VAARCHARR2(20);插入數(shù)數(shù)據(jù)INSEERT INNTO MMM VALUUES (11, A);INSSERT IINTO MMN VALLUES (1, BB);執(zhí)行行：MERGGE INTTO MN AUSINNG MM BON(AA.ID=BB.ID)WWHEN MMATCHEED THEENUPDAATE SEET A.IID = BB.IDWHHEN NOOT MATTCHED THENIINSERTT VALUUES(B.ID, BB.NAMEE);ON(A.ID=B.ID)報(bào)錯(cuò):無效效的標(biāo)識(shí)符,這個(gè)錯(cuò)誤提提示有些誤導(dǎo)導(dǎo)嫌疑，原因因是on子句句的使用的字字段不

29、能夠用用于updaate，即OOraclee不允許更新新用于連接的的列修改：MERRGE INNTO MNN AUSIING MMM BON(A.ID=B.ID)WHEN MATCHHED THHENUPDDATE SSET A.NAME = B.NNAMEWHHEN NOOT MATTCHED THENIINSERTT VALUUES(B.ID, BB.NAMEE);ON(A.ID=B.ID)再插入：IINSERTT INTOO MM VVALUESS (1, C);再執(zhí)行：MEERGE IINTO MMN AUSSING MMM BONN(A.IDD=B.IDD)WHENN MATCCHE

30、D TTHENUPPDATE SET AA.NAMEE = B.NAMEWWHEN NNOT MAATCHEDD THENNINSERRT VALLUES(BB.ID, B.NAMME);ONN(A.IDD=B.IDD)報(bào)錯(cuò)，原原因無法在源源表中獲得一一組穩(wěn)定的行行4）更新同同一張表的數(shù)數(shù)據(jù)。需要注注意下細(xì)節(jié)，因因?yàn)榭赡苌婕凹暗絬sinng的數(shù)據(jù)集集為nulll,所以要使使用counnt()函數(shù)數(shù)。MERGGE INTTO mn aUSINNG (seelect countt(*) cco froom mn wheree mn.IID=4) bON (b.co0)-這里使用了了countt和，

31、注注意下，想下下為什么！WWHEN MMATCHEED THEENUPDAATESETT a.NAAME = Ewhhere aa.ID=44WHEN NOT MMATCHEED THEENINSEERTVALLUES (4, EE);計(jì)算中位(間)值一、問題計(jì)算一列數(shù)字值值的中間值（中中間值就是一一組有序元素素中間成員的的值）。假設(shè)有如下數(shù)據(jù)據(jù)：create tablee empIInfo( name vvarchaar2(200), sal nuumber(6)insert into empInnfo vaalues(王宏,800);insert into empInnfo vaalues

32、(留心會(huì),11000);insert into empInnfo vaalues(張三,1500);insert into empInnfo vaalues(李薇霞,30000);insert into empInnfo vaalues(大大村,25000);insert into empInnfo vaalues(楊偉,2750);select * froom emppInfo orderr by sall王宏800留心會(huì)11000張三15000大大村25000楊偉27500李薇霞30000中位數(shù)應(yīng)為：22000=(1500+2500)/2二、解決方案除了Oraclle解決方案案（用函數(shù)計(jì)計(jì)

33、算中間數(shù)）之之外，其他所所有解決方案案都是以Roozenshhtein、AAbramoovich和和Birgeer在Opttimiziing Trransacct-SQLL: Advvancedd Proggrammiing Teechniqques (SQL FForum Presss, 19997)中描述述的方法為基基礎(chǔ)的。與傳傳統(tǒng)的自聯(lián)接接相比，窗口口函數(shù)的引入入，使解決方方案更為有效效。1. 通用求中中位數(shù)的方法法使用自聯(lián)接查找找中間數(shù)：其其原理是：首先進(jìn)行自連接接，獲得笛卡卡爾積；其次次計(jì)算各記錄錄sal字段段的差并轉(zhuǎn)換換為符號(hào)函數(shù)數(shù)，即：sign(e.sal - d.sal)以及各

34、個(gè)記記錄sal字字段是否相同同，用下列語語句實(shí)現(xiàn)：case whhen e.sal = d.sal thhen 1 else 00 end；然后后再進(jìn)行分組組，并計(jì)算組組內(nèi)sal字字段的平均值值。如果一列數(shù)為奇奇數(shù)個(gè)數(shù)，則則這列數(shù)必定定關(guān)于中間一一個(gè)數(shù)的abbs(sum(sign(e.sal - d.sal)成對(duì)稱。如如果一列數(shù)為為偶數(shù)個(gè)數(shù)，則則這列數(shù)必定定關(guān)于中間的的兩個(gè)數(shù)成對(duì)對(duì)稱。select avg(sal) from ( -找到中中間位置的ssal值 selecct e.sal from empInnfo e, empIInfo dd groupp by e.sal havinng s

35、um(casee when ee.sal = d.sal thhen 1 else 00 end )= abs(sum(sign(e.sal - d.sal)-endd abs)t根據(jù)第一個(gè)自聯(lián)聯(lián)接表EMPP計(jì)算中間數(shù)數(shù)，而該表返返回了所有工工資的笛卡兒兒積（GROOUP BYY E.SSAL會(huì)去掉掉重復(fù)值）。HHAVINGG子句使用函函數(shù)SUM計(jì)計(jì)算E.SAAL等于D.SAL的次次數(shù)；如果這這個(gè)值大于等等于E.SAAL且大于DD.SAL次次數(shù)，那么該該行就是中間間數(shù)。在SEELECT列列表中加入SSUM就可以以觀察到這種種情況：select avg(sal) from ( -找到中中間位置的

36、ssal值 selecct e.sal,countt(e.sal) ncouunt,sum(caase when ee.sal = d.sal thhen 1 else 00 end) sum00,abs(suum(sign(e.sal - d.sal) ) abs00 from empInnfo e, empIInfo dd groupp by e.sal havinng sum(casee when ee.sal = d.sal thhen 1 else 00 end )= abs(sum(sign(e.sal - d.sal)-endd abs)t80071611007141500712

37、22007102500712275071430007162.DB2,OOraclee 數(shù)據(jù)庫(kù)使用窗口函數(shù)CCOUNT(*) OVVER和ROOW_NUMMBER，查查找中間數(shù)：select avg(saal) frrom(-通過分析函函數(shù)為查詢添添加3列：ttotal，hhalf，xxh seleect saal, counnt(*) over() tottal, floorr(counnt(*) over()/2) halff, rrow_nuumber() oveer(ordder by sall) xhh fromm empIInfo)wheree (mod(tootal,22)=0 a

38、nd xhh in(hallf,hallf+1)偶數(shù)行，取取中間兩行的的值 or (mod(tootal,22)=1 and xhh=halff+1) 奇數(shù)行，取取中間行的值值執(zhí)行結(jié)果：2000=(15000+2500)/23.Oraclle數(shù)據(jù)庫(kù)使用函數(shù)MEDDIAN（OOraclee Dataabase 10g）或PERCCENTILLE_CONNT（Oraacle9ii 及以上版版本）：-使用于Orracle110g 及以上版本本select mediaan(sall) ffrom eempInffo -使用于Orracle99i 及以上上版本 selecct perceentilee_

39、contt(0.5) wwithinn grouup(orderr by sall) from eempInffo 說明：對(duì)于Orracle88i Dattabasee，使用DBB2解決方案案。對(duì)于Orracle88i Dattabasee之前的版本本，可以采用用PostggreSQLL/MySQQL解決方案案。4.SQL SServerr 數(shù)據(jù)庫(kù)(ssqlserrver20005以上版版本)使用窗口函數(shù)CCOUNT(*) OVVER和ROOW_NUMMBER，可可得到中間數(shù)數(shù)：-創(chuàng)建數(shù)據(jù)表表create tablee empIInfo( name vvarchaar(20), sal inn

40、t)-向表插入數(shù)數(shù)據(jù)insert into eempInffo vallues(王宏,800);insert into eempInffo vallues(留心會(huì),1100);insert into eempInffo vallues(張三,1500);insert into eempInffo vallues(李薇霞,3000);insert into eempInffo vallues(大大村,2500);insert into eempInffo vallues(楊偉,2750);insert into eempInffo vallues(華陽,2200);-查詢select * fro

41、m eempInffo ordder by sall王宏8000留心會(huì)11000張三15000華陽22000大大村25000楊偉27550李薇霞30000-獲得中位數(shù)數(shù)select avg(sal) from( selecct sall, couunt(*) over() totaal, flooor(countt(*) over()/2) halff, roww_numbber() over(orderr by sall) xh from empInnfo) twhere (xh %2=0 and xhh in(hallf,half+1)-偶數(shù)數(shù)行，取中間間兩行的值 oor (xh % 2=

42、1 and xhh=half+1) -數(shù)行，取取中間行的值值三、各種方案總總結(jié)1.DB2和SSQL Seerver 解決方案的的唯一差別是是語法的稍許許不同：SQQL Serrver用“%”求模，而DDB2和Orracle使使用MOD函函數(shù)；其余的的都相同。2.內(nèi)聯(lián)視圖XX返回三個(gè)不不同的計(jì)數(shù)值值，TOTAAL和hallfT，還用用到由ROWW_NUMBBER生成的的RN。這些些附加列有助助于求解中間間數(shù)。檢驗(yàn)內(nèi)內(nèi)聯(lián)視圖X的的結(jié)果集，就就會(huì)看到這些些列表示的意意義：3.根據(jù)第一一個(gè)自聯(lián)接表表EMP計(jì)算算中間數(shù)，而而該表返回了了所有工資的的笛卡兒積（GGROUP BY E.SSAL會(huì)去掉掉重復(fù)值

43、）。HHAVINGG子句使用函函數(shù)SUM計(jì)計(jì)算E.SAAL等于D.SAL的次次數(shù)；如果這這個(gè)值大于等等于E.SAAL且大于DD.SAL次次數(shù)，那么該該行就是中間間數(shù)。在SEELECT列列表中加入SSUM就可以以觀察到這種種情況： -找到中中間位置的ssal值 selecct e.sal,countt(e.sal) ncouunt,sum(caase when ee.sal = d.sal thhen 1 else 00 end) sum00,abs(suum(sign(e.sal - d.sal) ) abs00 from empInnfo e, empIInfo dd groupp by e

44、.salOracle在Oraclee Dataabase 10g或Orracle99i Dattabasee中，可以使使用Oraccle提供的的函數(shù)計(jì)算中中間數(shù)；對(duì)于于Oraclle8i DDatabaase，可以以采用DB22解決方案；其他版本只只能采用通用用求中位數(shù)的的解決方案。顯顯然可以用MMEDIANN函數(shù)計(jì)算中中間值，用PPERCENNTILE_CONT函函數(shù)也可以計(jì)計(jì)算中間值就就不那么顯而而易見了。傳傳遞給PERRCENTIILE_COONT的值00.5是一個(gè)個(gè)百分比值。子子句WITHHIN GRROUP (ORDERR BY SSAL)確定定PERCEENTILEE_CONTT要

45、搜索哪些些有序行（記記住，中間值值就是一組已已排序值的中中間值）。返返回的值就是是搜索的有序序行中符合給給定百分比（在在這個(gè)例子中中是0.5，因因?yàn)槠鋬蓚€(gè)邊邊界值分別為為0和1）的的值。總之，通用解決決方案適用于于各種版本各各種類型的數(shù)數(shù)據(jù)庫(kù)，但效效率較低；Oracle 提供了響應(yīng)應(yīng)的函數(shù)，但但對(duì)版本要求求較高；Sqqlservver和Orracle 一樣，對(duì)版版本有要求。計(jì)算眾數(shù)的方法法一、準(zhǔn)備數(shù)據(jù)select * from ttb張三語文774張三數(shù)學(xué)883張三物理993李四語文774李四數(shù)學(xué)884劉洋語文881劉洋數(shù)學(xué)882劉洋物理886二、求眾數(shù)的一一般方法：-首先構(gòu)造CCTEwith

46、 tbbGroupp as (selectt xm,countt(*) tootal ffrom ttb grooup by xm)-找出具有最最大值的所有有記錄(即眾眾數(shù))方案1：select * from ttbGrouupwhere ttotal=(selectt max(totall) from ttbGrouup 求各組內(nèi)的的最大值)顯示結(jié)果：劉洋3張三3方案2：select * from (selectt xm,totall,densee_rankk() over(orderr by tottal deesc) mc frrom tbbGroupp)twhere mmc=1顯示結(jié)果

47、：劉洋31張三31即該組數(shù)中有22個(gè)眾數(shù)。注意：一組數(shù)可能能沒有眾數(shù)！此時(shí)CTEE將返回空集集！查詢出連續(xù)的記記錄有一個(gè)表結(jié)構(gòu)，fphm,kshm,其記錄如下：2014,00000000012014,00000000022014,00000000032014,00000000042014,00000000052014,00000000072014,00000000082014,00000000092013,00000012202013,00000012212013,00000012222013,00000012242013,0000001225(第二個(gè)字段內(nèi)內(nèi)可能是連續(xù)續(xù)的數(shù)據(jù)，可可能存在斷點(diǎn)

48、點(diǎn)。)要求查詢出連續(xù)續(xù)的記錄來。就就像下面的這這樣？2014,0000000001,00000000552014,0000000009,00000000772013,0000001220,00000012222013,0000001224,0000001255SELECT b.fphhm, MIIN (b.kshm) Starrt_HM, MAX (b.ksshm) EEnd_HMMFROM (SELECT a.*, TO_NUUMBER (a.kshhm - RROWNUMM) ccFROM (SSELECTT * FRROM t ORDERR BY ffphm, kshm) a) bGRO

49、UP BBY b.ffphm, b.ccSELECT b.fphhm, MIIN (b.kshm) Starrt_HM, MAX (b.ksshm) EEnd_HMMFROM (SELECCT a.*, TO_NUMBEER (a.kshm - ROWWNUM) cc FROM (SELEECT * FROM t ORDDER BYY fphmm, kshhm) a) bGROUP BY b.fphm, b.cccFPHM STTART_HHM ENDD_HM- - -2013 0000001220 00000012222013 0000001224 00000012552014 000000

50、0001 00000000552014 0000000007 0000000099/查詢所有物品的的最后出庫(kù)時(shí)時(shí)間物品 出庫(kù)庫(kù)時(shí)間 a 20005-3-4a 20005-122-3a 20006-10-8a 20007-5-1b 20006-1-1b 22006-110-2b 22007-66-24c 22006-112-2c 22007-33-8-要求結(jié)果物品 最最后出庫(kù)時(shí)間間a 20077-5-1b 20077-6-244c 20077-3-8 select mc,maax(rq) fromm SQLnnumberr grouup by mcselect * froom SQLLnumbe

51、er as a where existts (selecct topp 1 * from SQLnuumber as b wheree b.mcc=a.mcc ordeer by rq deesc)select * froom taable as a exitss (sellect ttop 1 * froom taable as b wheree b.物品=aa.物品品 ordder byy 出庫(kù)時(shí)時(shí)間 deesc)農(nóng)歷】計(jì)算方法法- (陽歷轉(zhuǎn)陰歷歷) 因?yàn)檗r(nóng)歷的日期期，是由天文文學(xué)家推算出出來的，到現(xiàn)現(xiàn)在只有到22049年的的，以后的有有了還可以加加入！首先建建一表，放初初始化資料1.創(chuàng)建

52、表CRREATE TABLEE SolaarDataa( yeearId int nnot nuull, datta chaar(7) not nnull, daataIntt int not nnull)2.插入數(shù)據(jù)IINSERTT INTOO SolaarDataa SELEECT 19900,00 x04bdd8,199416 UUNION ALL SSELECTT 19011,0 x004ae0,191668UNIOON ALLL SELEECT 19902,00 x0a570,423552 UNIION ALLL SELLECT 11903,0 x0544d5,221717UUNIO

53、N ALL SSELECTT 19044,0 x00d260,538556 UNIION ALLL SELLECT 11905,0 x0d9950,555632UUNION ALL SSELECTT 19066,0 x116554,914776 UNIION ALLL SELLECT 11907,0 x0566a0,222176UUNION ALL SSELECTT 19088,0 x009ad0,396332 UNIION ALLL SELLECT 11909,0 x0555d2,221970UUNION ALL SSELECTT 19100,0 x004ae0,191668 UNIION

54、ALLL SELLECT 11911,0 x0a5b6,424222UNIOON ALLL SELEECT 19912,00 x0a4d0,421992 UNIION ALLL SELLECT 11913,0 x0d2250,553840UUNION ALL SSELECTT 19144,0 x11d255,1193381 UNNION AALL SEELECT 1915,0 x0bb540,464000UNIONN ALL SELECCT 19116,0 xx0d6a0,554944 UNIONN ALL SELECCT 19117,0 xx0ada22,444450UNIION ALLL

55、SELLECT 11918,0 x0955b0,338320 UNIONN ALL SELECCT 19119,0 xx149777,843343UNIION ALLL SELLECT 11920,0 x049970,118800 UNIONN ALL SELECCT 19221,0 xx0a4b0,421660UNIOON ALLL SELEECT 19922,00 x0b4bb5,466261 UUNION ALL SSELECTT 19233,0 x006a50,272116UNIOON ALLL SELEECT 19924,00 x06d440,277968 UUNION ALL SS

56、ELECTT 19255,0 x11ab54,1093396UNIION ALLL SELLECT 11926,0 x02bb60,111104 UNIONN ALL SELECCT 19227,0 xx095700,382256UNIION ALLL SELLECT 11928,0 x0522f2,221234 UNIONN ALL SELECCT 19229,0 xx049700,188800UNIION ALLL SELLECT 11930,0 x065566,225958 UNIONN ALL SELECCT 19331,0 xx0d4a0,554432UUNION ALL SSELE

57、CTT 19322,0 x00ea50,599884 UNIION ALLL SELLECT 11933,0 x06ee95,228309UUNION ALL SSELECTT 19344,0 x005ad0,232448 UNIION ALLL SELLECT 11935,0 x02bb60,111104UUNION ALL SSELECTT 19366,0 x1186e3,1000067 UNNION AALL SEELECT 1937,0 x0992e0,376000UNIONN ALL SELECCT 19338,0 xx1c8d7,1169951 UNNION AALL SEELEC

58、T 1939,0 x0cc950,515366UNIONN ALL SELECCT 19440,0 xx0d4a0,554432 UNIONN ALL SELECCT 19441,0 xx1d8a6,11209988UNIONN ALL SELECCT 19442,0 xx0b5500,464416 UNNION AALL SEELECT 1943,0 x0556a0,221766UNIONN ALL SELECCT 19444,0 xx1a5b4,1079956 UNNION AALL SEELECT 1945,0 x0225d0,9680UUNION ALL SSELECTT 19466,

59、0 x0092d0,375884 UNIION ALLL SELLECT 11947,0 x0d22b2,553938UUNION ALL SSELECTT 19488,0 x00a950,433444 UNIION ALLL SELLECT 11949,0 x0b5557,446423UUNION ALL SSELECTT 19500,0 x006ca0,278008 UNIION ALLL SELLECT 11951,0 x0b5550,446416UUNION ALL SSELECTT 19522,0 x115355,868669 UNIION ALLL SELLECT 11953,0

60、x04dda0,119872UUNION ALL SSELECTT 19544,0 x00a5d0,424448 UNIION ALLL SELLECT 11955,0 x145573,883315UUNION ALL SSELECTT 19566,0 x0052d0,212000 UNIION ALLL SELLECT 11957,0 x0a9a8,443432UUNION ALL SSELECTT 19588,0 x00e950,597228 UNIION ALLL SELLECT 11959,0 x06aaa0,227296UUNION ALL SSELECTT 19600,0 x00a