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

1、case 子句的用用途语法： caase whenn 条件1 thenn 值1 wheen条件22 theen 值2。when条条件n tthen 值nelse 值elsseendd case子子句相当于于一个具有有多个选择择的语句，在在多个不同同的条件中中，选择其其中之一，与与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 语句更新新列值执行本语句句将性别为为男的伙食食补助修改改为1200，女修改改为1000UPDATTE TBB_EMPP2SET 伙伙食补助=CASEE WHEEN 性别别=男 theen 1220 WHEEN 性别别=女 theen 1000END当列cj的的值为744时，修改改其值为880, 为97时时，修改其其值为1000,其它它值不变updatte tbb set ccj=(casse cjj whenn 74 thenn 80when 97 thenn 100else cj eend);当列kc的

3、的值为语文时，修改改列cj=cj+66, 为物理时，修改改其值为ccj+4,其它值不不变updatte tbb set ccj=(casse whenn kc=语文 thenn cj+6when kc=物理 thenn cj+4else cj eend);/利用CASSE 语句句进行更新新列值执行本语句句将性别为为男的伙食食补助修改改为1200，女修改改为1000UPDATTE TBB_EMPP2SET 伙伙食补助=CASEE WHEEN 性别别=男 theen 1220 WHEEN 性别别=女 theen 1000END当列cj的的值为744时，修改改其值为880, 为97时时，修改其其值为

4、1000,其它它值不变updatte tbb set ccj=(casse cjj whenn 74 thenn 80when 97 thenn 100else cj eend);当列kc的的值为语文时，修改改列cj=cj+66, 为物理时，修改改其值为ccj+4,其它值不不变updatte tbb set ccj=(casse whenn kc=语文 thenn cj+6when kc=物理 thenn cj+4else cj eend);case语语句在SQQL中的使使用方法 Case具具有两种格格式。简单单Casee函数和CCase搜搜索函数。 1.简单CCase函函数CASE sex

5、WHENN 1 THEEN 男男 WHENN 2 THEEN 女女ELSE 其他 ENDD2.搜索ccase函函数CASE WHENN sexx = 1 TTHEN 男 WHENN sexx = 2 TTHEN 女ELSE 其他 ENDD这两种方式式，可以实实现相同的的功能。简简单Casse函数的的写法相对对比较简洁洁，但是和和Casee搜索函数数相比，功功能方面会会有些限制制，比如写写判断式。 还有一个需需要注意的的问题，CCase函函数只返回回第一个符符合条件的的值，剩下下的Casse部分将将会被自动动忽略。 -比如说说，下面这这段SQLL，你永远远无法得到到“第二类”这个结果果CASE

6、WHENN coll_1 IN ( a, b) THHEN 第一类 WHENN coll_1 IIN (a) THHEN 第二类ELSE其他 ENDCase使使用集锦一，已知数数据按照另另外一种方方式进行分分组，分析析。 下面的CCase语语句用在GGroupp by 子句中有如下数据据:(为了了看得更清清楚，我并并没有使用用国家代码码，而是直直接用国家家名作为PPrimaary KKey) 国家（coountrry） 人人口（poopulaationn） 中国 600 美国 100 加拿大 100 英国 200 法国 300 日本 250 德国 200 墨西哥 50 印度 250 根据国家人

7、人口数据，统统计亚洲和和北美洲的的人口数量量。应该得得到下面这这个结果。 洲 人人口 亚洲 11100 北美洲 250 其他 7700 想要解决这这个问题，你你会怎么做做？生成一一个带有洲洲Codee的Vieew，是一一个解决方方法，但是是这样很难难动态的改改变统计的的方式。 如果使用CCase函函数，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;同样的，我我们也可以以用这个方方法来判断断工资的等等级，并统统计每一等等级的人数数。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 )女职员的条条件倒是符符合了，男男职员就无无法输入了了。这是一一个非常经经典的用法法！四，根据条条件有选择择的UPDDATE。 下面的CCase语语句用在uupdatte子句中中，如果进进行两次更更新，则可可能会产生生错误！例，有如下下更新条件件 工资50000以上的的职员，工工资减少110% 工资在20000到44600之之间的职员员，工资增增加15% 很容易考虑虑的是选择择执行两次次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;这里要注意意一点，最最后一行的的ELSEE sallary是是必需的，要要是没有这这行，不符符合这两个个条件的人人的工资将将会被写成成NUl

12、ll,那可就就大事不妙妙了。在CCase函函数中Ellse部分分的默认值值是NULLL，这点点是需要注注意的地方方。 五变更主主键列的值值本质上任任然是Caase语句句在upddate中中的应用一般情况下下，要想把把两条数据据的Priimaryy keyy,a和bb交换，需需要经过临临时存储，拷拷贝，读回回数据的三三个过程，要要是使用CCase函函数的话，一一切都变得得简单多了了。 p_keyy coll_1 ccol_22 a 1 张三 b 2 李四 c3 王五 假设有如上上数据，需需要把主键键a和b相相互交换。用Casse函数来来实现的话话，代码如如下 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);同样的也可可以交换两两个Uniique key。需要注意意的是，如如果有需要要交换主键键的情况发发生，多半半是当初对对这个表的的设计进行行得不够到到位，建议议检查表的的设计是否否妥当。 六，两个表表数据是否否一致的检检查。 Casee函数中，可可以使用下下列子句：BETWEEEN AND , LIKE,IS NUULL,IN, NNOT IINEXISTTS 和NOT EXISS

14、TSCase函函数不同于于DECOODE函数数。在Caase函数数中，可以以使用BEETWEEEN,LIIKE,IIS NUULL,IIN,EXXISTSS等等。比比如说使用用IN,EEXISTTS，可以以进行子查查询，从而而 实现更更多的功能能。 下面具个例例子来说明明，有两个个表，tbbl_A,tbl_B，两个个表中都有有keyCCol列。现在我们们对两个表表进行比较较，tbll_A中的的keyCCol列的的数据如果果在tbll_B的kkeyCool列的数数据中可以以找到， 返回结果果Mattchedd,如果果没有找到到，返回结结果Unnmatcched。 要实现下面面这个功能能，可以使使

15、用下面两两条语句 1.使用IIN的时候候SELECCT keeyColl,CASE WHENN keyyCol IN ( SELEECT kkeyCool FRROM ttbl_BB ) whenn中使用in 子句句THEN MattcheddELSE Unmmatchhed END LabeelFROM tbl_A;2.使用EEXISTTS的时候候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的结结果是相同同的。也可可以使用NNOT IIN和NOOT EXXISTSS，但是这这个时候要要注意NUULL的情情况。 七，在Caase函数数中使用合合计函数 假设有下面面一个表学号(sttd_idd) 课程程ID(cclasss_id) 课程名名(claass_nname) 主修fflag（mmain_classs_fllg) 100 11 经济学学 Y 100 22 历史学学 N 200 22 历史学学 N 200 33 考古学学 Y 200 44 计算机机 N 300 44 计算机机 N 400 55

17、化学 N 500 66 数学 N 有的学生选选择了同时时修几门课课程(1000,2000)也有有的学生只只选择了一一门课程(300,400,500)。选修多多门课程的的学生，要要选择一门门课程作为为主修，主主修flaag里面写写入 Y。只选择一一门课程的的学生，主主修flaag为N(实际上要要是写入YY的话，就就没有下面面的麻烦事事了，为了了举例子，还还请多多包包含)。 现在我们要要按照下面面两个条件件对这个表表进行如下下查询 ：只选修一门门课程的学学生，返回回那门课程程的ID 选修多门课课程的学生生，返回所所选的主课课程ID 简单的想法法就是，执执行两条不不同的SQQL语句进进行查询。 -条

18、件11：只选择择了一门课课程的学生生SELECCT sttd_idd, MAAX(cllass_id) AS mmain_classsFROM StuddentcclasssGROUPP BY std_idHAVINNG COOUNT(*) = 1;执行结果11 STD_IID MAINN_claass- -300 4400 5500 6-条件22：选择多多门课程的的学生SELECCT sttd_idd, cllass_id AAS maain_cclasssFROM StuddentcclasssWHEREE maiin_cllass_flg = YY ;执行结果22 STD_IID MMA

19、IN_classs- -100 11200 33如果使用CCase函函数，我们们只要一条条SQL语语句就可以以解决问题题，具体如如下所示 SELECCT sstd_iid,CASE WHENN COUUNT(*) = 1 THHEN MMAX(cclasss_id) -只选择一一门课程的的学生的情情况ELSE MAX(CASEE WHEEN maain_cclasss_flgg = YTHEN classs_iddELSE NULLL ENDD)END AAS maain_cclasssFROM StuddentcclasssGROUPP BY std_id;运行结果 STD_IID MAIN

20、N_claass- -100 1200 3300 4400 5500 6通过在Caase函数数中嵌套CCase函函数，在合合计函数中中使用Caase函数数等方法，我我们可以轻轻松的解决决这个问题题。使用CCase函函数给我们们带来了更更大的自由由度。 最后提醒一一下使用CCase函函数的新手手注意不要要犯下面的的错误 CASE col_1WHEN 1 THEEN RRighttWHEN NULLL THHEN WronngEND在这个语句句中Wheen Nuull这一一行总是返返回unkknownn，所以永永远不会出出现Wroong的情情况。因为为这句可以以替换成WWHEN col_1 = N

21、ULLL，这是一一个错误的的用法，这这个时候我我们应该选选择用WHHEN ccol_11 IS NULLL。正确的的做法是：CASE col_1WHEN 1 THENN RiightWHEN IS NUULL THENN WrrongENDcoaleesce函函数语法：cooalessce(paraam1,pparamm2)当第一个参参数parram1(通常为字字段)为nulll时，返返回第二个个参数paaram22的值，否否则，返回回第一个参参数的值.这个函数数与NVLL函数功能能相同。例： seelectt coallescee(bh,0) bhhs frrom cchineese sel

22、eect nnvl(bbh,0) bhhs frrom cchineese从表中随机机返回n条条记录实现此功能能需要使用用函数dbbms_rrandoom.vaalue()作为oorderr by子子句的参数数，这样查询将将依赖于函函数的结果果进行排序序。利用这这个函数可可以实现随随机出题例1：随机机产生1000条歇后后语记录Selecct * from (selecct xhh,conntentt,keyys frrom xxhy oorderr by dbbms_rrandoom.vaalue()Wheree rownnum=100;例2：利用用函数dbbms_rrandoom.vaalu

23、e()产生1100个随随机数Oraclle 100g方法selecct dbbms_rrandoom.vaalue() frrom ddual connnect by rownnum=100Oraclle 9ii方法selecct dbbms_rrandoom.vaalue() frrom all_objeects rownnum=100根据学生成成绩排名次次要求：要求求输出前nn名学生，同同一成绩名名次相同。原理：利用用densse_raank()这个分析析函数进行行名次排列列主要数据如如下Xmsccorec72x83b83d65h96n57y90m99v1000输出前5名名Xmsccore

24、 mcn571d652c723x834b834y905selecct * fromm( sselecct xmm,scoore,ddensee_rannk()oover(ordeer by sccore) mc fromm stuudenttscorre t)wherre mcc=5oraclle使用mergge更新或插插入数据使用merrge比传传统的先判判断再选择择插入或更更新快很多多。1）主主要功能提提供有条件件地更新和和插入数据据到数据库库表中如果果该行存在在，执行一一个UPDDATE操操作，如果果是一个新新行，执行行INSEERT操作作 避免了了分开更新新 提高性性能并易于于使用 在

25、数据据仓库应用用中十分有有用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的注意事事项：创建建测试表：CREAATE TTABLEE MM (ID NUMBBER, NAMEE VARRCHARR2(200);CCREAT

29、TE TAABLE MN (ID NNUMBEER, NNAME VARCCHAR22(20);插入入数据INNSERTT INTTO MMM VALLUES (1, A);INSSERT INTOO MN VALUUES (1, B);执行：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)报错:无效的标标识符,这这个错误提

30、提示有些误误导嫌疑，原因是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);再执行：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)报错，原因无法在源表中获得一组稳定的行4）更新同一张表的数据。需要注意下细节，因为可能涉及到using的数据集为null,所以要使用count()函数。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);计算中位(间)值一、问题计算一列数数字值的中中间值（中中间值就是是一组有序序元素中间间成员的值值）。假设有如下下数据：creatte taable empIInfo( namee varrcharr2(200), sal numbber(66)inserrt innto eempInnfo vvaluees(王王宏,8800);inserrt innto eempInnfo vvaluees(留留心会,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留心会11100张三15500大大村22500杨伟27750李薇霞33000中位数应为为：20000=(11500+25000)/2二、解决方方案除了Oraacle解解决方案（用用函数计算算中间数）之之外，其他他所有解决决方案都是是以Rozzen

34、shhteinn、Abrramovvich和和Birgger在OOptimmizinng Trransaact-SSQL: Advaancedd Proogrammmingg Tecchniqques (SQLL Forrum PPresss, 19997)中中描述的方方法为基础础的。与传传统的自联联接相比，窗口函数的引入，使解决方案更为有效。1. 通用用求中位数数的方法使用自联接接查找中间间数：其原原理是：首先进行自自连接，获获得笛卡尔尔积；其次次计算各记记录sall字段的差差并转换为为符号函数数，即：siggn(e.sal - d.sal)以及各个个记录saal字段是是否相同，用用下列语句

35、句实现：case whenn e.sal = d.sal thenn 1 ellse 00 endd；然后再再进行分组组，并计算算组内saal字段的的平均值。如果一列数数为奇数个个数，则这这列数必定定关于中间间一个数的的abs(sum(signn(e.sal - d.sal)成对称称。如果一一列数为偶偶数个数，则则这列数必必定关于中中间的两个个数成对称称。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根据第一个个自联接表表EMP计计算中间数数，而该表表返回了所所有工资的的笛卡儿积积（GROOUP BBY E.SAL会会去掉重复复值）。HHAVINNG子句使使用函数SSUM计算算E.SAAL等于DD.SALL的次数；如果这个个值大于等等于E.SSAL且大大于D.SSAL次数数，那么该该行就是中中间数。在在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 数据库使用窗口函函数COUUNT(*) OVVER和RROW_NNUMBEER，查找找中间数：selecct avg(ssal) fromm(-通过分分析函数为为查询添加加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)偶数行，取取中间两行行的值 or (modd(tottal,22)=1 and xxh=haalf+11) 奇数行，取取中间行的的值执行结结果：2000=(11500+25000)/23.Oraacle数数据库使用函数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 说明：对于于Oraccle8ii Dattabasse，使用用DB2解解决方案。对于Orraclee8i DDatabbase之之前的版本本，可以采采用PosstgreeSQL/MySQQL解决方方案。4.SQLL Serrver 数据库(sqlsserveer20005以上版版本)使用窗口函函数COUUNT(*) OVVER和RROW_NNUMBEER，可得得到中间数数：-创建数数据表creatte tablle

41、 emmpInffo( namee varcchar(20), sal int)-向表插插入数据inserrt intoo emppInfoo vallues(王宏,800);inserrt intoo emppInfoo vallues(留心会会,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留心会11100张三11500华阳22200大大村22500杨伟22750李薇霞33000-获得中中位数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)-偶偶数行，取取中间两行行的值 or (xh % 2=1 andd xh=halff+1) -数行行，取中间间行的值三、各种方方案总结1.DB22和SQLL Serrver 解决方案案的唯一差差别是语法法的稍许不不同：SQQL Seerverr用“%”求模，而而DB2和和Oraccle使用用MOD函函数；其余余的都相同同。2.内联视视图X返回回三个不同同的计数值值，TOTTAL和hhalfTT，还用到到由ROWW_NUMMBER生生成的RNN。这些附附加列有助助于求解中中间数。检检验内联视视图X的结

44、结果集，就就会看到这这些列表示示的意义：3.根据据第一个自自联接表EEMP计算算中间数，而而该表返回回了所有工工资的笛卡卡儿积（GGROUPP BY E.SAL会会去掉重复复值）。HHAVINNG子句使使用函数SSUM计算算E.SAAL等于DD.SALL的次数；如果这个个值大于等等于E.SSAL且大大于D.SSAL次数数，那么该该行就是中中间数。在在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提供的函函数计算中中间数；对对于Oraacle88i Daatabaase，可可以采用DDB2解决决方案；其其他版本只只能采用通通用求中位位数的解决决方案。显显然可以用用MEDIIAN函数数计算中间间值，用PPERCEENTILLE_COONT函数数也可以计计算

46、中间值值就不那么么显而易见见了。传递递给PERRCENTTILE_CONTT的值0.5是一个个百分比值值。子句WWITHIIN GRROUP (ORDDER BBY SAAL)确定定PERCCENTIILE_CCONT要要搜索哪些些有序行（记记住，中间间值就是一一组已排序序值的中间间值）。返返回的值就就是搜索的的有序行中中符合给定定百分比（在在这个例子子中是0.5，因为为其两个边边界值分别别为0和11）的值。总之，通用用解决方案案适用于各各种版本各各种类型的的数据库，但但效率较低低；Oraclle 提供供了响应的的函数，但但对版本要要求较高；Sqlsserveer和Orraclee 一样，对对

47、版本有要要求。计算众数的的方法一、准备数数据selecct * fromm tb张三语文文74张三数学学83张三物理理93李四语文文74李四数学学84刘洋语文文81刘洋数学学82刘洋物理理86二、求众数数的一般方方法：-首先构构造CTEEwith tbGrroup as (seleect xxm,counnt(*) tottal ffrom tb ggroupp by xmm)-找出具具有最大值值的所有记记录(即众众数)方案1：selecct * fromm tbGGrouppwheree tottal=(seleect max(totaal) fromm tbGGroupp 求各组内内的最大

48、值值)显示结果：刘洋3张三3方案2：selecct * fromm (seleect xxm,totaal,densse_raank() overr(ordeer by tootal descc) mc fromm tbGGroupp)twheree mc=1显示结果：刘洋31张三31即该组数中中有2个众数。注意：一组数可可能没有众众数！此时时CTE将将返回空集集！查询出连续续的记录有一个表结结构，fpphm,kkshm,其记录如如下：2014,00000000112014,00000000222014,00000000332014,00000000442014,00000000552014,

49、00000000772014,00000000882014,00000000992013,00000012002013,00000012112013,00000012222013,00000012442013,0000001255(第二个字字段内可能能是连续的的数据，可可能存在断断点。)要求查询出出连续的记记录来。就就像下面的的这样？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/查询所有物物品的最后后出库时间间物品 出库时间间 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-要求结果物品 最后出出库时间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 出库时时间 ddesc)农历】计算算方法- (阳阳历转阴历历) 因为农历的的日期，是是由天文学学家推算出出来的，到到现在只有有到20449年的，以以后的有了了还可以加加入！首先先建一表，放放初始化资资料1.创建表表CREAATE TTABLEE SollarDaata( yyearIId innt noot nuull, ddata charr(7) not nulll, dattaIntt intt nott nulll)2.插入数数据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