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

1、case 子句句的用途語法：casewheen條件1theen值1whenn條件2 thhen值2。when條件nn thenn值nelse值ellseenddcase子句相相當于一個具具有多個選擇擇的語句，在在多個不同的的條件中，選選擇其中之一一，與javva語言的ccase語句句類似。例：如果表chhinesee的字段bhh為nulll,則返回00，如果字段段bh為1,則返回999，字段bhh為其它值，原原樣返回。selectccasewhhen bhh isnulllthen0when bbh=1then999else bbh ennd bhss, xhh,codee ffrom cch

2、inesse 一、利用CASSE 語句更更新列值執(zhí)行本語句將性性別為男的伙伙食補助修改改為120，女女修改為1000UPDATE TB_EMMP2SET 伙食補補助=CASSE WHEEN 性別=男 tthen 1120 WHHEN 性別別=女 then 100END當列cj的值為為74時，修修改其值為880,為997時，修改改其值為1000,其它值值不變update tb set cj=(casee cjwheen74thenn80when97tthen1000else cjj end);當列kc的值為為語文時，修改列列cj=cjj+6,為為物理時，修改其其值為cj+4,其它值值不變upda

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

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

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

6、另外一種種方式進行分分組，分析。 下面的Casee語句用在GGroup by 子句句中有如下數(shù)據(jù):(為了看得更更清楚，我并并沒有使用國國家代碼，而而是直接用國國家名作為PPrimarry Keyy) 國家（counntry） 人口（poopulattion） 中國 6600美國 1100 加拿大 100 英國 2200 法國 3300 日本 2250德國 2200 墨西哥 50 印度 2250根據(jù)國家人口數(shù)數(shù)據(jù)，統(tǒng)計亞亞洲和北美洲洲的人口數(shù)量量。應該得到到下面這個結結果。 洲 人口 亞洲 11000 北美洲 2550 其他 7000 想要解決這個問問題，你會怎怎么做？生成成一個帶有洲洲Code

7、的的View，是是一個解決方方法，但是這這樣很難動態(tài)態(tài)的改變統(tǒng)計計的方式。 如果使用Casse函數(shù)，SSQL代碼如如下: SELECT SUM(popullationn), CASEE counntry WWHEN 中國 THHEN 亞亞洲 WWHEN 印度 THHEN 亞亞洲 WWHEN 日本 THHEN 亞亞洲 WWHEN 美國 THHEN 北北美洲 WWHEN 加拿大 THENN 北美洲洲 WWHEN 墨西哥 THENN 北美洲洲 ELSEE 其他 ENDFROM Tablle_AGROUP BBY CASSE couuntry WWHEN 中國 THHEN 亞亞洲 WWHEN 印度 T

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

9、_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 )女職員的條件倒倒是符合了，男男職員就無法法輸入了。這這是一個非常常經(jīng)典的用法法！四，根據(jù)條件有有選擇的UPPDATE。 下面的Casee語句用在uupdatee子句中，如如果進行兩次次

10、更新，則可可能會產(chǎn)生錯錯誤！例，有如下更新新條件 工資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 = 50000 THENN salaary * 0.9WHEN saa

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

12、函數(shù)的話，一一切都變得簡簡單多了。 p_key ccol_1 col_22 a 1 張張三 b 2 李李四 c3 王五五 假設有如上數(shù)據(jù)據(jù)，需要把主主鍵a和b相相互交換。用用Case函函數(shù)來實現(xiàn)的的話，代碼如如下 UPDATE SomeTTableSET p_kkey =CASE WHHEN p_key = aTTHEN bWHEN p_key = bTTHEN aELSE p_key EENDWHERE pp_key IN (a, b);同樣的也可以交交換兩個Unnique key。需需要注意的是是，如果有需需要交換主鍵鍵的情況發(fā)生生，多半是當當初對這個表表的設計進行行得不夠到位位，建議檢查

13、查表的設計是是否妥當。 六，兩個表數(shù)據(jù)據(jù)是否一致的的檢查。 Case函數(shù)中中，可以使用用下列子句：BETWEENN ANDD , LIKE,IS NULLL,IN,NOT INEXISTS和和NOT EXXISTSCase函數(shù)不不同于DECCODE函數(shù)數(shù)。在Casse函數(shù)中，可可以使用BEETWEENN,LIKEE,IS NNULL,IIN,EXIISTS等等等。比如說使使用IN,EEXISTSS，可以進行行子查詢，從從而 實現(xiàn)更更多的功能。 下面具個例子來來說明，有兩兩個表，tbbl_A,ttbl_B，兩兩個表中都有有keyCool列。現(xiàn)在在我們對兩個個表進行比較較，tbl_A中的keeyC

14、ol列列的數(shù)據(jù)如果果在tbl_B的keyyCol列的的數(shù)據(jù)中可以以找到， 返返回結果MMatcheed,如果果沒有找到，返返回結果UUnmatcched。 要實現(xiàn)下面這個個功能，可以以使用下面兩兩條語句 1.使用IN的的時候SELECT keyCool,CASE WHHEN keeyCol IN ( SSELECTT keyCCol FRROM tbbl_B )when中中使用in 子句THEN MMatcheedELSE UUnmatcched END LLabelFROM tbbl_A;2.使用EXIISTS的時時候SELECT keyCool,CASE WHHEN EXXISTS ( S

15、ELLECT * FROMM tbl_BWHERE ttbl_A.keyCool = ttbl_B.keyCool )THEN MMatcheedELSE UUnmatcched END LLabelFROM tbbl_A;使用IN和EXXISTS的的結果是相同同的。也可以以使用NOTT IN和NNOT EXXISTS，但但是這個時候候要注意NUULL的情況況。 七，在Casee函數(shù)中使用用合計函數(shù) 假設有下面一個個表學號(std_id) 課課程ID(cclass_id) 課課程名(cllass_nname) 主修flaag（maiin_claass_fllg) 100 1 經(jīng)經(jīng)濟學 Y 10

16、0 2 歷歷史學 N 200 2 歷歷史學 N 200 3 考考古學 Y 200 4 計計算機 N 300 4 計計算機 N 400 5 化化學 N 500 6 數(shù)數(shù)學 N有的學生選擇了了同時修幾門門課程(1000,2000)也有的學學生只選擇了了一門課程(300,4400,5000)。選修修多門課程的的學生，要選選擇一門課程程作為主修，主主修flagg里面寫入 Y。只選擇擇一門課程的的學生，主修修flag為為N(實際上上要是寫入YY的話，就沒沒有下面的麻麻煩事了，為為了舉例子，還還請多多包含含)。 現(xiàn)在我們要按照照下面兩個條條件對這個表表進行如下查查詢 ：只選修一門課程程的學生，返返回那門課

17、程程的ID 選修多門課程的的學生，返回回所選的主課課程ID 簡單的想法就是是，執(zhí)行兩條條不同的SQQL語句進行行查詢。 -條件1：只只選擇了一門門課程的學生生SELECT std_iid, MAAX(claass_idd) AS main_classsFROM SttudenttclasssGROUP BBY stdd_idHAVING COUNTT(*) = 1;執(zhí)行結果1 STD_ID MAIIN_claass- -300 4400 5500 6-條件2：選選擇多門課程程的學生SELECT std_iid, cllass_iid AS main_classsFROM Sttudenttcl

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

19、ROUP BBY stdd_id;運行結果 STD_ID MAIIN_claass- -100 1200 3300 4400 5500 6通過在Casee函數(shù)中嵌套套Case函函數(shù)，在合計計函數(shù)中使用用Case函函數(shù)等方法，我我們可以輕松松的解決這個個問題。使用用Case函函數(shù)給我們帶帶來了更大的的自由度。 最后提醒一下使使用Casee函數(shù)的新手手注意不要犯犯下面的錯誤誤 CASE cool_1WHEN 1 THEEN RiightWHEN NUULL TTHEN WronggEND在這個語句中WWhen NNull這一一行總是返回回unknoown，所以以永遠不會出出現(xiàn)Wronng的情況。因

20、為這句可可以替換成WWHEN ccol_1 = NULLL，這是一一個錯誤的用用法，這個時時候我們應該該選擇用WHHEN cool_1 IIS NULLL。正確的的做法是：CASE cool_1WHEN 11 THEEN RiightWHEN ISS NULLL THEEN WrrongENDcoalescce函數(shù)語法：coallesce(paramm1,parram2)當?shù)谝粋€參數(shù)pparam11(通常為字字段)為nulll時，返回第第二個參數(shù)pparam22的值，否則則，返回第一一個參數(shù)的值值.這個函數(shù)數(shù)與NVL函函數(shù)功能相同同。例：selecctcoallesce(bh,0) bhss

21、fromm chinnese selecct nvll(bh,00) bhss fromm chinnese從表中隨機返回回n條記錄實現(xiàn)此功能需要要使用函數(shù)ddbms_rrandomm.valuue()作為orrder bby子句的參參數(shù)，這樣查詢將依賴賴于函數(shù)的結結果進行排序序。利用這個個函數(shù)可以實實現(xiàn)隨機出題題例1：隨機產(chǎn)生生100條歇歇后語記錄Select * from (select xh,coontentt,keyss fromm xhy orderrby dbmms_ranndom.vvalue()Whereroownum=100;例2：利用函數(shù)數(shù)dbms_randoom.vall

22、ue()產(chǎn)生1000個隨機數(shù)數(shù)Oracle 10g方法select dbms_randoom.vallue() from ddual cconnecctbyrownnum=100Oracle 9i方法select dbms_randoom.vallue() fromalll_objjectsrrownumm=100根據(jù)學生成績排排名次要求：要求輸出出前n名學生生，同一成績績名次相同。原理：利用deense_rrank()這個分析函函數(shù)進行名次次排列主要數(shù)據(jù)如下Xmscorrec72x83b83d65h96n57y90m99v100輸出前5名Xmscorre mcn571d652c723x834

23、b834y905select * froom( sellect xxm,scoore,deense_rrank()over(orderrby scoore) mmc froom stuudentsscore t)wheree mc=5oracle使使用mergee更新或插入入數(shù)據(jù)使用mergee比傳統(tǒng)的先先判斷再選擇擇插入或更新新快很多。11）主要功能能提供有條件件地更新和插插入數(shù)據(jù)到數(shù)數(shù)據(jù)庫表中如如果該行存在在，執(zhí)行一個個UPDATTE操作，如如果是一個新新行，執(zhí)行IINSERTT操作 避免了分分開更新 提高性能能并易于使用用 在數(shù)據(jù)倉倉庫應用中十十分有用2）MMERGE語語句的語法如如下：

24、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 emplloyeess eON (c.emmployeee_id=e.empployeee_

25、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.hiree_datee,e.joob_id,e.sallary,mmissioon_pctt,e.m

26、aanagerr_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 MAATCHEDD THENNINSERRT VALLUES(ee.emplloyee_id,e

27、.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的注意事事項：創(chuàng)建測測試表：CRREATE TABLEE MM (ID NUUMBER, NAMEE VARCCHAR2(20);CREATTE TABBLE MNN (ID NUMBEER, NAAME VAARCHARR2(20);插入數(shù)數(shù)據(jù)INSEERT INNTO MMM VAL

28、UUES (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)報錯:無效效的標識符,這個錯誤提提示有些誤導導嫌疑，原因因是on子句句的使用的字字段不能夠用用于updaate，即OOraclee不允許更新新用于連接的的列修改：MERRGE I

29、NNTO 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 MATCCHED TTHENUPPDATE SET AA.NAMEE = B.NAMEWWHEN NNOT

30、 MAATCHEDD THENNINSERRT VALLUES(BB.ID, B.NAMME);ONN(A.IDD=B.IDD)報錯，原原因無法在源源表中獲得一一組穩(wěn)定的行行4）更新同同一張表的數(shù)數(shù)據(jù)。需要注注意下細節(jié)，因因為可能涉及及到usinng的數(shù)據(jù)集集為nulll,所以要使使用counnt()函數(shù)數(shù)。MERGGE INTTO mn aUSINNG (seelect countt(*) cco froom mn wheree mn.IID=4) bON (b.co0)-這里使用了了countt和，注注意下，想下下為什么！WWHEN MMATCHEED THEENUPDAATESETT a

31、.NAAME = Ewhhere aa.ID=44WHEN NOT MMATCHEED THEENINSEERTVALLUES (4, EE);計算中位(間)值一、問題計算一列數(shù)字值值的中間值（中中間值就是一一組有序元素素中間成員的的值）。假設有如下數(shù)據(jù)據(jù)：create tablee empIInfo( name vvarchaar2(200), sal nuumber(6)insert into empInnfo vaalues(王宏,800);insert into empInnfo vaalues(留心會,11000);insert into empInnfo vaalues(張三,15

32、00);insert into empInnfo vaalues(李薇霞,30000);insert into empInnfo vaalues(大大村,25000);insert into empInnfo vaalues(楊偉,2750);select * froom emppInfo orderrby sall王宏800留心會11000張三15000大大村25000楊偉27500李薇霞30000中位數(shù)應為：22000=(1500+2500)/2二、解決方案除了Oraclle解決方案案（用函數(shù)計計算中間數(shù)）之之外，其他所所有解決方案案都是以Roozenshhtein、Abrammovich

33、h和Birgger在Opptimizzing TTransaact-SQQL: Addvanceed Proogrammming TTechniiques (SQL Forumm Presss, 19997)中描描述的方法為為基礎的。與與傳統(tǒng)的自聯(lián)聯(lián)接相比，窗窗口函數(shù)的引引入，使解決決方案更為有有效。1.通用求中位位數(shù)的方法使用自聯(lián)接查找找中間數(shù)：其其原理是：首先進行自連接接，獲得笛卡卡爾積；其次次計算各記錄錄sal字段段的差并轉(zhuǎn)換換為符號函數(shù)數(shù)，即：sign(e.sal - d.sal)以及各個記記錄sal字字段是否相同同，用下列語語句實現(xiàn)：casewheen e.sal = d.sal t

34、hhen 1 else 00 end；然后后再進行分組組，并計算組組內(nèi)sal字字段的平均值值。如果一列數(shù)為奇奇數(shù)個數(shù)，則則這列數(shù)必定定關于中間一一個數(shù)的abbs(sum(sign(e.sal - d.sal)成對稱。如如果一列數(shù)為為偶數(shù)個數(shù)，則則這列數(shù)必定定關于中間的的兩個數(shù)成對對稱。selectaavg(sal)from(-找到中間位位置的sall值select e.salfrom emmpInfoo e, empIInfo ddgroupbyy e.salhavingssum(casewheen e.sal = d.sal thhen 1 else 00 end)=abs(sum(sign

35、(e.sal - d.sal)-endd abs)t根據(jù)第一個自聯(lián)聯(lián)接表EMPP計算中間數(shù)數(shù)，而該表返返回了所有工工資的笛卡兒兒積（GROOUP BYY E.SSAL會去掉掉重復值）。HAVINNG子句使用用函數(shù)SUMM計算E.SSAL等于DD.SAL的的次數(shù)；如果果這個值大于于等于E.SSAL且大于于D.SALL次數(shù)，那么么該行就是中中間數(shù)。在SSELECTT列表中加入入SUM就可可以觀察到這這種情況：selectaavg(sal)from(-找到中間位位置的sall值select e.sal,countt(e.sal) ncouunt,sum(cassewhenn e.sal = d.sa

36、l thhen 1 else 00 end) sum00,abs(summ(sign(e.sal - d.sal) abs00from emmpInfoo e, empIInfo ddgroupbyy e.salhavingssum(casewheen e.sal = d.sal thhen 1 else 00 end)=abs(sum(sign(e.sal - d.sal)-endd abs)t80071161100714150071222007102500712275071430007162.DB2,OOraclee數(shù)據(jù)庫使用窗口函數(shù)CCOUNT(*) OVVER和ROOW_NUMMBER

37、，查查找中間數(shù)：selectaavg(saal) frrom(-通過分析函函數(shù)為查詢添添加3列：ttotal，hhalf，xxhseleect saal, counnt(*) over() tottal, floorr(counnt(*) over()/2) halff, rrow_nuumber() oveer(ordderby sall) xhhfrom eempInffo)wheree (mod(tootal,22)=0and xhh in(hallf,hallf+1)偶數(shù)行，取取中間兩行的的值or (mod(tootal,22)=1and xhh=halff+1)奇數(shù)行，取取中間行的值

38、值執(zhí)行結果：2000=(15000+2500)/23.Oraclle數(shù)據(jù)庫使用函數(shù)MEDDIAN（OOraclee Dataabase 10g）或PERCCENTILLE_CONNT（Oraacle9ii 及以上版版本）：-使用于Orracle110g及以上上版本selectmmediann(sal) frrom emmpInfoo -使用于Orracle99i 及以上上版本sellectpeercenttile_ccont(0.5) wwithinn grouup(orderrby sall) from eempInffo 說明：對于Orracle88i Dattabasee，使用DBB2解

39、決方案案。對于Orracle88i Dattabasee之前的版本本，可以采用用PostggreSQLL/MySQQL解決方案案。4.SQL SServerr 數(shù)據(jù)庫(ssqlserrver20005以上版版本)使用窗口函數(shù)CCOUNT(*) OVVER和ROOW_NUMMBER，可可得到中間數(shù)數(shù)：-創(chuàng)建數(shù)據(jù)表表createttable empInnfo(namevarrchar(20), sal innt)-向表插入數(shù)數(shù)據(jù)insertiinto eempInffo vallues(王宏,800);insertiinto eempInffo vallues(留心會,1100);insertii

40、nto eempInffo vallues(張三,1500);insertiinto eempInffo vallues(李薇霞,3000);insertiinto eempInffo vallues(大大村,2500);insertiinto eempInffo vallues(楊偉,2750);insertiinto eempInffo vallues(華陽,2200);-查詢select*from eempInffo ordderby sall王宏800留心會11000張三15000華陽22000大大村25000楊偉27500李薇霞30000-獲得中位數(shù)數(shù)selectaavg(sal)f

41、rom(select sal,count(*)overr() tottal,floor(ccount(*)oveer()/2) halff, roww_numbber()overr(orderrby sall) xhfrom emmpInfoo) twhere(xxh %2=0 and xhh in(hallf,half+1)-偶數(shù)數(shù)行，取中間間兩行的值or(xh % 2=1 and xhh=half+1)-數(shù)行，取取中間行的值值三、各種方案總總結1.DB2和SSQL Seerver 解決方案的的唯一差別是是語法的稍許許不同：SQQL Serrver用“%”求模，而DDB2和Orracle使使

42、用MOD函函數(shù)；其余的的都相同。2.內(nèi)聯(lián)視圖XX返回三個不不同的計數(shù)值值，TOTAAL和hallfT，還用用到由ROWW_NUMBBER生成的的RN。這些些附加列有助助于求解中間間數(shù)。檢驗內(nèi)內(nèi)聯(lián)視圖X的的結果集，就就會看到這些些列表示的意意義：3.根據(jù)第一個個自聯(lián)接表EEMP計算中中間數(shù)，而該該表返回了所所有工資的笛笛卡兒積（GGROUP BY E.SSAL會去掉掉重復值）。HAVINNG子句使用用函數(shù)SUMM計算E.SSAL等于DD.SAL的的次數(shù)；如果果這個值大于于等于E.SSAL且大于于D.SALL次數(shù)，那么么該行就是中中間數(shù)。在SSELECTT列表中加入入SUM就可可以觀察到這這種情況

43、：-找到中間位位置的sall值select e.sal,countt(e.sal) ncouunt,sum(cassewhenn e.sal = d.sal thhen 1 else 00 end) sum00,abs(summ(sign(e.sal - d.sal) abs00from emmpInfoo e, empIInfo ddgroupbyy e.salOracle在Oraclee Dataabase 10g或Orracle99i Dattabasee中，可以使使用Oraccle提供的的函數(shù)計算中中間數(shù)；對于于Oraclle8i DDatabaase，可以以采用DB22解決方案；其他

44、版本只只能采用通用用求中位數(shù)的的解決方案。顯然可以用用MEDIAAN函數(shù)計算算中間值，用用PERCEENTILEE_CONTT函數(shù)也可以以計算中間值值就不那么顯顯而易見了。傳遞給PEERCENTTILE_CCONT的值值0.5是一一個百分比值值。子句WIITHIN GROUPP (ORDDER BYY SAL)確定PERRCENTIILE_COONT要搜索索哪些有序行行（記住，中中間值就是一一組已排序值值的中間值）。返回的值就就是搜索的有有序行中符合合給定百分比比（在這個例例子中是0.5，因為其其兩個邊界值值分別為0和和1）的值?？傊?，通用解決決方案適用于于各種版本各各種類型的數(shù)數(shù)據(jù)庫，但效效

45、率較低；Oracle 提供了響應應的函數(shù)，但但對版本要求求較高；Sqqlservver和Orracle 一樣，對版版本有要求。計算眾數(shù)的方法法一、準備數(shù)據(jù)select*from ttb張三語文774張三數(shù)學883張三物理993李四語文774李四數(shù)學884劉洋語文881劉洋數(shù)學882劉洋物理886二、求眾數(shù)的一一般方法：-首先構造CCTEwith tbbGroupp as(selectt xm,countt(*) tootal ffrom ttb grooupby xm)-找出具有最最大值的所有有記錄(即眾眾數(shù))方案1：select*from ttbGrouupwhere ttotal=(sel

46、ectmmax(totall)from ttbGrouup求各組內(nèi)的的最大值)顯示結果：劉洋3張三3方案2：select*from(select xm,totall,densee_rankk()overr(orderrby tottal deesc) mc frrom tbbGroupp)twhere mmc=1顯示結果：劉洋31張三31即該組數(shù)中有22個眾數(shù)。注意：一組數(shù)可能能沒有眾數(shù)！此時CTEE將返回空集集！查詢出連續(xù)的記記錄有一個表結構，fphm,kshm,其記錄如下：2014,2014,2014,2014,2014,2014,2014,2014,2013,2013,2013,2013

47、,2013,(第二個字段內(nèi)內(nèi)可能是連續(xù)續(xù)的數(shù)據(jù)，可可能存在斷點點。)要求查詢出連續(xù)續(xù)的記錄來。就像下面的的這樣？2014,2014,2013,2013,SELECT 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) bGROUP BBY b.ffphm, b.ccSELECT b.fphhm, MIIN (b.kshm) Star

48、rt_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 2013 2014 2014 /查詢所有物品的的最后出庫時時間物品出庫時間 a 20005-3-4a 20005-122-3a 20006-10-8a 20007-5-1b 20006-1-1b 22006-110-2b 2

49、2007-66-24c 22006-112-2c 22007-33-8-要求結果物品最后出庫時時間a 20077-5-1b 20077-6-244c 20077-3-8select mc,maax(rq) fromm SQLnnumberr grouup by mcselect * froom SQLLnumbeer 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 t

50、top 1 * froom taable as b wheree b.物品=aa.物品品 ordder byy 出庫時時間 deesc)農(nóng)歷】計算方法法- (陽歷轉(zhuǎn)陰歷歷)因為農(nóng)歷的日期期，是由天文文學家推算出出來的，到現(xiàn)現(xiàn)在只有到22049年的的，以后的有有了還可以加加入！首先建建一表，放初初始化資料1.創(chuàng)建表CRREATE TABLEE SolaarDataa( yeearId int nnot nuull, datta chaar(7) not nnull, daataIntt int not nnull)2.插入數(shù)據(jù)IINSERTT INTOO SolaarDataa SELEECT

51、19900,00 x04bdd8,199416 UUNION ALL SSELECTT 19011,0 x004ae0,191668UNIOON ALLL SELEECT 19902,00 x0a570,423552 UNIION ALLL SELLECT 11903,0 x0544d5,221717UUNION ALL SSELECTT 19044,0 x00d260,538556 UNIION ALLL SELLECT 11905,0 x0d9950,555632UUNION ALL SSELECTT 19066,0 x116554,914776 UNIION ALLL SELLECT 1

52、1907,0 x0566a0,222176UUNION ALL SSELECTT 19088,0 x009ad0,396332 UNIION ALLL SELLECT 11909,0 x0555d2,221970UUNION ALL SSELECTT 19100,0 x004ae0,191668 UNIION ALLL SELLECT 11911,0 x0a5b6,424222UNIOON ALLL SELEECT 19912,00 x0a4d0,421992 UNIION ALLL SELLECT 11913,0 x0d2250,553840UUNION ALL SSELECTT 19144

53、,0 x11d255, UNIION ALLL SELLECT 11915,0 x0b5540,446400UUNION ALL SSELECTT 19166,0 x00d6a0,554944 UNIONN ALL SELECCT 19117,0 xx0ada22,444450UNIION ALLL SELLECT 11918,0 x0955b0,338320 UNIONN ALL SELECCT 19119,0 xx149777,843343UNIION ALLL SELLECT 11920,0 x049970,118800 UNIONN ALL SELECCT 19221,0 xx0a4b

54、0,421660UNIOON ALLL SELEECT 19922,00 x0b4bb5,466261 UUNION ALL SSELECTT 19233,0 x006a50,272116UNIOON ALLL SELEECT 19924,00 x06d440,277968 UUNION ALL SSELECTT 19255,0 x11ab54,UNIOON ALLL SELEECT 19926,00 x02b660,111104 UUNION ALL SSELECTT 19277,0 x009570,382556UNIOON ALLL SELEECT 19928,00 x052ff2,211

55、234 UUNION ALL SSELECTT 19299,0 x004970,188000UNIOON ALLL SELEECT 19930,00 x065666,255958 UUNION ALL SSELECTT 19311,0 x00d4a0,554432UUNION ALL SSELECTT 19322,0 x00ea50,599884 UNIION ALLL SELLECT 11933,0 x06ee95,228309UUNION ALL SSELECTT 19344,0 x005ad0,232448 UNIION ALLL SELLECT 11935,0 x02bb60,1111

56、04UUNION ALL SSELECTT 19366,0 x1186e3, UNIION ALLL SELLECT 11937,0 x0922e0,337600UUNION ALL SSELECTT 19388,0 x11c8d7, UNIION ALLL SELLECT 11939,0 x0c950,515336UNIOON ALLL SELEECT 19940,00 x0d4aa0,544432 UUNION ALL SSELECTT 19411,0 x11d8a6,UNIION ALLL SELLECT 11942,0 x0b5550,446416 UNIONN ALL SELECCT

57、 19443,0 xx056a0,221776UNIOON ALLL SELEECT 19944,00 x1a5b4, UNIION ALLL SELLECT 11945,0 x0255d0,99680UNNION AALL SEELECT 1946,0 x0992d0,375844 UNIOON ALLL SELEECT 19947,00 x0d2bb2,533938UNNION AALL SEELECT 1948,0 x0aa950,433444 UNIOON ALLL SELEECT 19949,00 x0b5557,466423UNNION AALL SEELECT 1950,0 x0

58、66ca0,278088 UNIOON ALLL SELEECT 19951,00 x0b5550,466416UNNION AALL SEELECT 1952,0 x155355,868699 UNIOON ALLL SELEECT 19953,00 x04daa0,199872UNNION AALL SEELECT 1954,0 x0aa5d0,424488 UNIOON ALLL SELEECT 19955,00 x145773,833315UNNION AALL SEELECT 1956,0 x0552d0,212000 UNIOON ALLL SELEECT 19957,00 x0a

59、9a8,443432UUNION ALL SSELECTT 19588,0 x00e950,597228 UNIION ALLL SELLECT 11959,0 x06aaa0,227296UUNION ALL SSELECTT 19600,0 x00aea6,447110 UNIION ALLL SELLECT 11961,0 x0abb50,443856UUNION ALL SSELECTT 19622,0 x004b60,192996 UNIION ALLL SELLECT 11963,0 x0aaae4,443748UUNION ALL SSELECTT 19644,0 x00a570

60、,423552 UNIION ALLL SELLECT 11965,0 x052260,221088UUNION ALL SSELECTT 19666,0 x00f263,620551 UNIION ALLL SELLECT 11967,0 x0d9950,555632UUNION ALL SSELECTT 19688,0 x005b57,233883 UNIION ALLL SELLECT 11969,0 x0566a0,222176UUNION ALL SSELECTT 19700,0 x0096d0,386008 UNIION ALLL SELLECT 11971,0 x04ddd5,1