管理信息化 ORACLEOracle 使用技
巧之 case 子句的用途
case子句的用途
语法：
case
when条件 1then值 1
when条件 2then值 2
。。。。。。
when条件 nthen值 n
else值 else
end
case 子句相当于一个具有多个选择的语句，在多个不同的条件中，选择其中之一，与 java
语言的 case语句类似。
例：如果表 chinese的字段 bh 为 null,则返回 0，如果字段 bh 为 1,则返回 99，字段 bh 为其
它值，原样返回。
select
case
whenbhisnullthen0
whenbh=1then99
elsebh
endbhs,
xh,code
fromchinese
一、利用 CASE语句更新列值
执行本语句将性别为男的伙食补助修改为 120，女修改为 100
UPDATETB_EMP2
SET伙食补助=CASEWHEN性别='男'then120
WHEN性别='女'then100
END
当列 cj的值为 74时，修改其值为 80,,为 97时，修改其值为 100,其它值不变
updatetb
setcj=(casecjwhen74then80
when97then100
elsecjend);
当列 kc的值为’语文’时，修改列 cj=cj+6,,为’物理’时，修改其值为 cj+4,其它值不变
updatetb
setcj=(casewhenkc='语文'thencj+6
whenkc='物理'thencj+4
elsecjend);
//////////////////////////////////////////////////
利用 CASE语句进行更新列值
执行本语句将性别为男的伙食补助修改为 120，女修改为 100
UPDATETB_EMP2
SET伙食补助=CASEWHEN性别='男'then120
WHEN性别='女'then100
END
当列 cj的值为 74时，修改其值为 80,,为 97时，修改其值为 100,其它值不变
updatetb
setcj=(casecjwhen74then80
when97then100
elsecjend);
当列 kc的值为’语文’时，修改列 cj=cj+6,,为’物理’时，修改其值为 cj+4,其它值不变
updatetb
setcj=(casewhenkc='语文'thencj+6
whenkc='物理'thencj+4
elsecjend);
case语句在 SQL中的使用方法
Case具有两种格式。简单 Case函数和 Case搜索函数。
1.简单 Case函数
CASEsex
WHEN'1'THEN'男'
WHEN'2'THEN'女'
ELSE'其他'END
2.搜索 case函数
CASEWHENsex='1'THEN'男'
WHENsex='2'THEN'女'
ELSE'其他'END
这两种方式，可以实现相同的功能。简单 Case函数的写法相对比较简洁，但是和 Case搜索
函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case 函数只返回第一个符合条件的值，剩下的 Case 部分将会被
自动忽略。
--比如说，下面这段 SQL，你永远无法得到“第二类”这个结果
CASEWHENcol_1IN('a','b')THEN'第一类'
WHENcol_1IN('a')THEN'第二类'
ELSE'其他'END
Case使用集锦
一，已知数据按照另外一种方式进行分组，分析。
下面的 Case语句用在 Groupby子句中
有如下数据:(为了看得更清楚，我并没有使用国家代码，而是直接用国家名作为 PrimaryKey)
国家（country）人口（population）
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250
根据国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。
洲人口
亚洲 1100
北美洲 250
其他 700
想要解决这个问题，你会怎么做？生成一个带有洲 Code 的 View，是一个解决方法，但是这
样很难动态的改变统计的方式。
如果使用 Case函数，SQL代码如下:
SELECTSUM(population),
CASEcountry
WHEN'中国'THEN'亚洲'
WHEN'印度'THEN'亚洲'
WHEN'日本'THEN'亚洲'
WHEN'美国'THEN'北美洲'
WHEN'加拿大'THEN'北美洲'
WHEN'墨西哥'THEN'北美洲'
ELSE'其他'END
FROMTable_A
GROUPBYCASEcountry
WHEN'中国'THEN'亚洲'
WHEN'印度'THEN'亚洲'
WHEN'日本'THEN'亚洲'
WHEN'美国'THEN'北美洲'
WHEN'加拿大'THEN'北美洲'
WHEN'墨西哥'THEN'北美洲'
ELSE'其他'END;
同样的，我们也可以用这个方法来判断工资的等级，并统计每一等级的人数。SQL代码如下；
SELECT
CASEWHENsalary<=500THEN'1'
WHENsalary>500ANDsalary<=600THEN'2'
WHENsalary>600ANDsalary<=800THEN'3'
WHENsalary>800ANDsalary<=1000THEN'4'
ELSENULLENDsalary_class,
COUNT(*)
FROMTable_A
GROUPBY
CASEWHENsalary<=500THEN'1'
WHENsalary>500ANDsalary<=600THEN'2'
WHENsalary>600ANDsalary<=800THEN'3'
WHENsalary>800ANDsalary<=1000THEN'4'
ELSENULLEND;
二，用一个 SQL语句完成不同条件的分组。
下面的 Case语句用在聚集函数(SUM)中,同时也起到了行转列的作用
有如下数据
国家（country）性别（sex）人口（population）
中国 1340
中国 2260
美国 145
美国 255
加拿大 151
加拿大 249
英国 140
英国 260
按照国家和性别进行分组，得出结果如下
国家男女
中国 340260
美国 4555
加拿大 5149
英国 4060
普通情况下，用 UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个 Select部
分)，而且 SQL语句会比较长。
下面是一个是用 Case函数来完成这个功能的例子
SELECTcountry,
SUM(CASEWHENsex='1'THEN
populationELSE0END),--男性人口
SUM(CASEWHENsex='2'THEN
populationELSE0END)--女性人口
FROMTable_A
GROUPBYcountry;
这样我们使用 Select，完成对二维表的输出形式，充分显示了 Case函数的强大。
三，在 Check中使用 Case函数。
下面的 Case语句用在表的约束规则中
在 Check 中使用 Case 函数在很多情况下都是非常不错的解决方法。可能有很多人根本就不
用 Check，那么我建议你在看过下面的例子之后也尝试一下在 SQL中使用 Check。
下面我们来举个例子
公司 A，这个公司有个规定，女职员的工资必须高于 1000 块。如果用 Check 和 Case 来表现
的话，如下所示
CONSTRAINTcheck_salaryCHECK
(CASEWHENsex='2'
THENCASEWHENsalary>1000
THEN1ELSE0END
ELSE1END=1)
如果单纯使用 Check，如下所示
CONSTRAINTcheck_salaryCHECK
(sex='2'ANDsalary>1000)
女职员的条件倒是符合了，男职员就无法输入了。这是一个非常经典的用法！
四，根据条件有选择的 UPDATE。
下面的 Case语句用在 update子句中，如果进行两次更新，则可能会产生错误！
例，有如下更新条件
工资 5000以上的职员，工资减少 10%
工资在 2000到 4600之间的职员，工资增加 15%
很容易考虑的是选择执行两次 UPDATE语句，如下所示
--条件 1
UPDATEPersonnel
SETsalary=salary*
WHEREsalary>=5000;
--条件 2
UPDATEPersonnel
SETsalary=salary*
WHEREsalary>=2000ANDsalary<4600;
但是事情没有想象得那么简单，假设有个人工资 5000 块。首先，按照条件 1，工资减少
10%，变成工资 4500。接下来运行第二个 SQL时候，因为这个人的工资是 4500在 2000到 4600
的范围之内，需增加 15%，最后这个人的工资结果是 5175,不但没有减少，反而增加了。如
果要是反过来执行，那么工资 4600 的人相反会变成减少工资。暂且不管这个规章是多么荒
诞，如果想要一个 SQL语句实现这个功能的话，我们需要用到 Case函数。代码如下:
UPDATEPersonnel
SETsalary=CASEWHENsalary>=5000
THENsalary*
WHENsalary>=2000ANDsalary<4600
THENsalary*
ELSEsalaryEND;
这里要注意一点，最后一行的 ELSEsalary 是必需的，要是没有这行，不符合这两个条件的
人的工资将会被写成 NUll,那可就大事不妙了。在 Case函数中 Else部分的默认值是 NULL，
这点是需要注意的地方。
五．变更主键列的值
本质上任然是 Case语句在 update中的应用
一般情况下，要想把两条数据的 Primarykey,a 和 b 交换，需要经过临时存储，拷贝，读回
数据的三个过程，要是使用 Case函数的话，一切都变得简单多了。
p_keycol_1col_2
a1张三
b2李四
c3王五
假设有如上数据，需要把主键 a和 b相互交换。用 Case函数来实现的话，代码如下
UPDATESomeTable
SETp_key=
CASEWHENp_key='a'THEN'b'
WHENp_key='b'THEN'a'
ELSEp_keyEND
WHEREp_keyIN('a','b');
同样的也可以交换两个 Uniquekey。需要注意的是，如果有需要交换主键的情况发生，多半
是当初对这个表的设计进行得不够到位，建议检查表的设计是否妥当。
六，两个表数据是否一致的检查。
Case函数中，可以使用下列子句：
BETWEENAND,
LIKE,
ISNULL,
IN,NOTIN
EXISTS和 NOTEXISTS
Case函数不同于 DECODE函数。在 Case函数中，可以使用 BETWEEN,LIKE,ISNULL,IN,EXISTS
等等。比如说使用 IN,EXISTS，可以进行子查询，从而实现更多的功能。
下面具个例子来说明，有两个表，tbl_A,tbl_B，两个表中都有 keyCol列。现在我们对两个
表进行比较，tbl_A 中的 keyCol 列的数据如果在 tbl_B 的 keyCol 列的数据中可以找到，返
回结果'Matched',如果没有找到，返回结果'Unmatched'。
要实现下面这个功能，可以使用下面两条语句
1.使用 IN的时候
SELECTkeyCol,
CASEWHENkeyColIN(SELECTkeyColFROMtbl_B)–when中使用 in子句
THEN'Matched'
ELSE'Unmatched'ENDLabel
FROMtbl_A;
2.使用 EXISTS的时候
SELECTkeyCol,
CASEWHENEXISTS(SELECT*FROMtbl_B
=)
THEN'Matched'
ELSE'Unmatched'ENDLabel
FROMtbl_A;
使用 IN和 EXISTS的结果是相同的。也可以使用 NOTIN和 NOTEXISTS，但是这个时候要注意
NULL的情况。
七，在 Case函数中使用合计函数
假设有下面一个表
学号(std_id)课程 ID(class_id)课程名(class_name)主修 flag（main_class_flg)
1001经济学 Y
1002历史学 N
2002历史学 N
2003考古学 Y
2004计算机 N
3004计算机 N
4005化学 N
5006数学 N
有的学生选择了同时修几门课程(100,200)也有的学生只选择了一门课程(300,400,500)。选
修多门课程的学生，要选择一门课程作为主修，主修 flag里面写入 Y。只选择一门课程的学
生，主修 flag 为 N(实际上要是写入 Y 的话，就没有下面的麻烦事了，为了举例子，还请多
多包含)。
现在我们要按照下面两个条件对这个表进行如下查询：
只选修一门课程的学生，返回那门课程的 ID
选修多门课程的学生，返回所选的主课程 ID
简单的想法就是，执行两条不同的 SQL语句进行查询。
--条件 1：只选择了一门课程的学生
SELECTstd_id,MAX(class_id)ASmain_class
FROMStudentclass
GROUPBYstd_id
HAVINGCOUNT(*)=1;
执行结果 1
STD_IDMAIN_class
----------------
3004
4005
5006
--条件 2：选择多门课程的学生
SELECTstd_id,class_idASmain_class
FROMStudentclass
WHEREmain_class_flg='Y';
执行结果 2
STD_IDMAIN_class
----------------
1001
2003
如果使用 Case函数，我们只要一条 SQL语句就可以解决问题，具体如下所示
SELECTstd_id,
CASEWHENCOUNT(*)=1THENMAX(class_id)--只选择一门课程的学生的情况
ELSEMAX(CASEWHENmain_class_flg='Y'
THENclass_id
ELSENULLEND
)
ENDASmain_class
FROMStudentclass
GROUPBYstd_id;
运行结果
STD_IDMAIN_class
----------------
1001
2003
3004
4005
5006
通过在 Case 函数中嵌套 Case 函数，在合计函数中使用 Case 函数等方法，我们可以轻松的
解决这个问题。使用 Case函数给我们带来了更大的自由度。
最后提醒一下使用 Case函数的新手注意不要犯下面的错误
CASEcol_1
WHEN1THEN'Right'
WHENNULLTHEN'Wrong'
END
在这个语句中 WhenNull这一行总是返回 unknown，所以永远不会出现 Wrong的情况。因为这
句可以替换成 WHENcol_1=NULL，这是一个错误的用法，这个时候我们应该选择用
WHENcol_1ISNULL。正确的做法是：
CASEcol_1
WHEN1THEN'Right'
WHENISNULLTHEN'Wrong'
END
coalesce函数
语法：coalesce(param1,param2)
当第一个参数 param1(通常为字段)为 null时，返回第二个参数 param2的值，否则，返回第
一个参数的值.这个函数与 NVL函数功能相同。
例：
selectcoalesce(bh,0)bhsfromchinese
selectnvl(bh,0)bhsfromchinese
从表中随机返回 n条记录
实现此功能需要使用函数 dbms_()作为 orderby子句的参数，
这样查询将依赖于函数的结果进行排序。利用这个函数可以实现随机出题
例 1：随机产生 100条歇后语记录
Select*
from(
selectxh,content,keysfromxhyorderbydbms_()
)
Whererownum<=100;
例 2：利用函数 dbms_()产生 100个随机数
Oracle10g方法
selectdbms_()fromdualconnectbyrownum<=100
Oracle9i方法
selectdbms_()fromall_objectsrownum<=100
根据学生成绩排名次
要求：要求输出前 n名学生，同一成绩名次相同。
原理：利用 dense_rank()这个分析函数进行名次排列
主要数据如下
Xmscore
c72
x83
b83
d65
h96
n57
y90
m99
v100
输出前 5名
Xmscoremc
n571
d652
c723
x834
b834
y905
select*from(
selectxm,score,dense_rank()over(orderbyscore)mcfromstudentscoret
)
wheremc<=5
oracle使用 merge更新或插入数据
使用 merge比传统的先判断再选择插入或更新快很多。
1）主要功能
提供有条件地更新和插入数据到数据库表中
如果该行存在，执行一个 UPDATE操作，如果是一个新行，执行 INSERT操作
—避免了分开更新
—提高性能并易于使用
—在数据仓库应用中十分有用
2）MERGE语句的语法如下：
MERGE[hint]INTO[schema.]table[t_alias]USING[schema.]
{table|view|subquery}[t_alias]ON(condition)
WHENMATCHEDTHENmerge_update_clause
WHENNOTMATCHEDTHENmerge_insert_clause;
还是看例子就知道怎么回事：
MERGEINTOcopy_empc
USINGemployeese
ON(_id=_id)
WHENMATCHEDTHEN
UPDATESET
_name=_name,
_name=_name,
_id=_id
WHENNOTMATCHEDTHEN
INSERTVALUES(_id,_name,_name,
,_number,_date,_id,
,_pct,_id,
_id);
MERGEINTOcopy_empc
USINGemployeese
ON(_id=_id)
WHENMATCHEDTHEN
UPDATESET
_name=_name,
_name=_name,
_id=_id
WHENNOTMATCHEDTHEN
INSERTVALUES(_id,_name,_name,
,_number,_date,_id,
,_pct,_id,
_id);
3）使用 merge的注意事项：
创建测试表：
CREATETABLEMM(IDNUMBER,NAMEVARCHAR2(20));
CREATETABLEMN(IDNUMBER,NAMEVARCHAR2(20));
插入数据
INSERTINTOMMVALUES(1,'A');
INSERTINTOMNVALUES(1,'B');
执行：
MERGEINTOMNA
USINGMMB
ON(=)
WHENMATCHEDTHEN
=
WHENNOTMATCHEDTHEN
INSERTVALUES(,);
ON(=)
报错:无效的标识符,这个错误提示有些误导嫌疑，原因是 on 子句的使用的字段不能够用于
update，即 Oracle不允许更新用于连接的列
修改：
MERGEINTOMNA
USINGMMB
ON(=)
WHENMATCHEDTHEN
=
WHENNOTMATCHEDTHEN
INSERTVALUES(,);
ON(=)
再插入：INSERTINTOMMVALUES(1,'C');
再执行：
MERGEINTOMNA
USINGMMB
ON(=)
WHENMATCHEDTHEN
=
WHENNOTMATCHEDTHEN
INSERTVALUES(,);
ON(=)
报错，原因无法在源表中获得一组稳定的行
4）更新同一张表的数据。需要注意下细节，因为可能涉及到 using的数据集为 null,所以要
使用 count()函数。
MERGEINTOmna
USING(selectcount(*)cofrommnwhere=4)b
ON(<>0)--这里使用了 count和<>，注意下，想下为什么！
WHENMATCHEDTHEN
UPDATE
='E'
=4
WHENNOTMATCHEDTHEN
INSERT
VALUES(4,'E');
计算中位(间)值
一、问题
计算一列数字值的中间值（中间值就是一组有序元素中间成员的值）。
假设有如下数据：
createtableempInfo
(
namevarchar2(20),
salnumber(6)
)
insertintoempInfovalues('王宏',800);
insertintoempInfovalues('留心会',1100);
insertintoempInfovalues('张三',1500);
insertintoempInfovalues('李薇霞',3000);
insertintoempInfovalues('大大村',2500);
insertintoempInfovalues('杨伟',2750);
select*fromempInfoorderbysal
王宏 800
留心会 1100
张三 1500
大大村 2500
杨伟 2750
李薇霞 3000
中位数应为：2000=(1500+2500)/2
二、解决方案
除了 Oracle解决方案（用函数计算中间数）之外，其他所有解决方案都是以 Rozenshtein、
Abramovich 和 Birger 在
OptimizingTransact-SQL:AdvancedProgrammingTechniques(SQLForumPress,1997) 中 描 述
的方法为基础的。与传统的自联接相比，窗口函数的引入，使解决方案更为有效。
1.通用求中位数的方法
使用自联接查找中间数：其原理是：
首先进行自连接，获得笛卡尔积；其次计算各记录 sal字段的差并转换为符号函数，
即：sign()以及各个记录 sal字段是否相同，用下列语句实现：
=；然后再进行分组，并计算组内 sal字段的平均值。
如果一列数为奇数个数，则这列数必定关于中间一个数的 abs(sum(sign()))成对
称。如果一列数为偶数个数，则这列数必定关于中间的两个数成对称。
selectavg(sal)from(
--找到中间位置的 sal值
fromempInfoe,empInfod
havingsum(
=
)>=abs(sum(sign()))--endabs
)t
根据第一个自联接表 EMP计算中间数，而该表返回了所有工资的笛卡儿积（会
去掉重复值）。HAVING子句使用函数 SUM计算 等于 的次数；如果这个值大于等
于 且大于 次数，那么该行就是中间数。在 SELECT 列表中加入 SUM 就可以观察
到这种情况：
selectavg(sal)from(
--找到中间位置的 sal值
,count()ncount,
sum(=)sum0,
abs(sum(sign()))abs0
fromempInfoe,empInfod
havingsum(
=
)>=abs(sum(sign()))--endabs
)t
800716
,Oracle数据库
使用窗口函数 COUNT(*)OVER和 ROW_NUMBER，查找中间数：
selectavg(sal)from(
--通过分析函数为查询添加 3列：total，half，xh
selectsal,
count(*)over()total,
floor(count(*)over()/2)half,
row_number()over(orderbysal)xh
fromempInfo
)
where(mod(total,2)=0andxhin(half,half+1))—偶数行，取中间两行的值
or(mod(total,2)=1andxh=half+1)—奇数行，取中间行的值
执行结果：
2000===(1500+2500)/2
数据库
使用函数 MEDIAN（OracleDatabase10g）或 PERCENTILE_CONT（Oracle9i及以上版本）：
--使用于 Oracle10g及以上版本
selectmedian(sal)fromempInfo
--使用于 Oracle9i及以上版本
selectpercentile_cont()
withingroup(orderbysal)
fromempInfo
说明：对于 Oracle8iDatabase，使用 DB2 解决方案。对于 Oracle8iDatabase 之前的版本，
可以采用 PostgreSQL/MySQL解决方案。
数据库(sqlserver2005以上版本)
使用窗口函数 COUNT(*)OVER和 ROW_NUMBER，可得到中间数：
--创建数据表
createtableempInfo
(
namevarchar(20),
salint
)
--向表插入数据
insertintoempInfovalues('王宏',800);
insertintoempInfovalues('留心会',1100);
insertintoempInfovalues('张三',1500);
insertintoempInfovalues('李薇霞',3000);
insertintoempInfovalues('大大村',2500);
insertintoempInfovalues('杨伟',2750);
insertintoempInfovalues('华阳',2200);
--查询
select*fromempInfoorderbysal
王宏 800
留心会 1100
张三 1500
华阳 2200
大大村 2500
杨伟 2750
李薇霞 3000
--获得中位数
selectavg(sal)from(
selectsal,
count(*)over()total,
floor(count(*)over()/2)half,
row_number()over(orderbysal)xh
fromempInfo
)t
where(xh%2=0andxhin(half,half+1))--偶数行，取中间两行的值
or(xh%2=1andxh=half+1)--数行，取中间行的值
三、各种方案总结
和 SQLServer解决方案的唯一差别是语法的稍许不同：SQLServer用“%”求模，而 DB2
和 Oracle使用 MOD函数；其余的都相同。
2.内联视图 X返回三个不同的计数值，TOTAL和 halfT，还用到由 ROW_NUMBER生成的 RN。这
些附加列有助于求解中间数。检验内联视图 X的结果集，就会看到这些列表示的意义：
3.根据第一个自联接表 EMP计算中间数，而该表返回了所有工资的笛卡儿积（
会去掉重复值）。HAVING子句使用函数 SUM计算 等于 的次数；如果这个值大于
等于 且大于 次数，那么该行就是中间数。在 SELECT 列表中加入 SUM 就可以观
察到这种情况：
--找到中间位置的 sal值
,count()ncount,
sum(=)sum0,
abs(sum(sign()))abs0
fromempInfoe,empInfod
Oracle
在 OracleDatabase10g或 Oracle9iDatabase中，可以使用 Oracle提供的函数计算中间数；
对于 Oracle8iDatabase，可以采用 DB2解决方案；其他版本只能采用通用求中位数的解决方
案。显然可以用 MEDIAN 函数计算中间值，用 PERCENTILE_CONT 函数也可以计算中间值就不
那 么 显 而 易 见 了 。 传 递 给 PERCENTILE_CONT 的 值 是 一 个 百 分 比 值 。 子 句
WITHINGROUP(ORDERBYSAL)确定 PERCENTILE_CONT 要搜索哪些有序行（记住，中间值就是一
组已排序值的中间值）。返回的值就是搜索的有序行中符合给定百分比（在这个例子中是
，因为其两个边界值分别为 0和 1）的值。
总之，通用解决方案适用于各种版本各种类型的数据库，但效率较低；
Oracle提供了响应的函数，但对版本要求较高；Sqlserver和 Oracle一样，对版本有要求。
计算众数的方法
一、准备数据
select*fromtb
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
刘洋语文 81
刘洋数学 82
刘洋物理 86
二、求众数的一般方法：
--首先构造 CTE
withtbGroupas
(selectxm,count(*)totalfromtbgroupbyxm)
--找出具有最大值的所有记录(即众数)
方案 1：
select*fromtbGroup
wheretotal=(
selectmax(total)fromtbGroup–求各组内的最大值
)
显示结果：
刘洋 3
张三 3
方案 2：
select*from(
selectxm,total,dense_rank()over(orderbytotaldesc)mcfromtbGroup
)t
wheremc=1
显示结果：
刘洋 31
张三 31
即该组数中有 2个众数。注意：一组数可能没有众数！此时 CTE将返回空集！
查询出连续的记录
有一个表结构，fphm,kshm,其记录如下：
2014,1
2014,2
2014,3
2014,4
2014,5
2014,7
2014,8
2014,9
2013,0
2013,1
2013,2
2013,4
2013,5
(第二个字段内可能是连续的数据，可能存在断点。)
要求查询出连续的记录来。就像下面的这样？
2014,1,5
2014,9,7
2013,0,2
2013,4,5
,MIN()Start_HM,MAX()End_HM
FROM(
SELECTa.*,TO_NUMBER(-ROWNUM)cc
FROM(SELECT*FROMtORDERBYfphm,kshm)a
)b
,
,MIN()Start_HM,MAX()End_HM
FROM(
SELECTa.*,TO_NUMBER(-ROWNUM)ccFROM(
SELECT*FROMtORDERBYfphm,kshm
)a
)b
,
FPHMSTART_HMEND_HM
--------------------------
///////////////////////////////////////////////////////////////////////////////
///////////////
查询所有物品的最后出库时间
物品出库时间
a2005-3-4
a2005-12-3
a2006-10-8
a2007-5-1
b2006-1-1
b2006-10-2
b2007-6-24
c2006-12-2
c2007-3-8
------------------------
要求结果
物品最后出库时间
a2007-5-1
b2007-6-24
c2007-3-8
selectmc,max(rq)fromSQLnumbergroupbymc
select*fromSQLnumberasa
whereexists(
selecttop1*=
)
select*from[table]asaexits(selecttop1*from[table]asbwhere[b].[ 物 品 ]=[a].[ 物
品]orderby[出库时间]desc)
农历】计算方法---(阳历转阴历)
因为农历的日期，是由天文学家推算出来的，到现在只有到 2049 年的，以后的有了还可以
加入！首先建一表，放初始化资料
1.创建表
CREATETABLESolarData
(
yearIdintnotnull,
datachar(7)notnull,
dataIntintnotnull
)
2.插入数据
INSERTINTO
SolarDataSELECT1900,'0x04bd8',19416UNIONALLSELECT1901,'0x04ae0',19168
UNIONALLSELECT1902,'0x0a570',42352UNIONALLSELECT1903,'0x054d5',21717
UNIONALLSELECT1904,'0x0d260',53856UNIONALLSELECT1905,'0x0d950',55632
UNIONALLSELECT1906,'0x16554',91476UNIONALLSELECT1907,'0x056a0',22176
UNIONALLSELECT1908,'0x09ad0',39632UNIONALLSELECT1909,'0x055d2',21970
UNIONALLSELECT1910,'0x04ae0',19168UNIONALLSELECT1911,'0x0a5b6',42422
UNIONALLSELECT1912,'0x0a4d0',42192UNIONALLSELECT1913,'0x0d250',53840
UNIONALLSELECT1914,'0x1d255',119381UNIONALLSELECT1915,'0x0b540',46400
UNIONALLSELECT1916,'0x0d6a0',54944UNIONALLSELECT1917,'0x0ada2',44450
UNIONALLSELECT1918,'0x095b0',38320UNIONALLSELECT1919,'0x14977',84343
UNIONALLSELECT1920,'0x04970',18800UNIONALLSELECT1921,'0x0a4b0',42160
UNIONALLSELECT1922,'0x0b4b5',46261UNIONALLSELECT1923,'0x06a50',27216
UNIONALLSELECT1924,'0x06d40',27968UNIONALLSELECT1925,'0x1ab54',109396
UNIONALLSELECT1926,'0x02b60',11104UNIONALLSELECT1927,'0x09570',38256
UNIONALLSELECT1928,'0x052f2',21234UNIONALLSELECT1929,'0x04970',18800
UNIONALLSELECT1930,'0x06566',25958UNIONALLSELECT1931,'0x0d4a0',54432
UNIONALLSELECT1932,'0x0ea50',59984UNIONALLSELECT1933,'0x06e95',28309
UNIONALLSELECT1934,'0x05ad0',23248UNIONALLSELECT1935,'0x02b60',11104
UNIONALLSELECT1936,'0x186e3',100067UNIONALLSELECT1937,'0x092e0',37600
UNIONALLSELECT1938,'0x1c8d7',116951UNIONALLSELECT1939,'0x0c950',51536
UNIONALLSELECT1940,'0x0d4a0',54432UNIONALLSELECT1941,'0x1d8a6',120998
UNIONALLSELECT1942,'0x0b550',46416UNIONALLSELECT1943,'0x056a0',22176
UNIONALLSELECT1944,'0x1a5b4',107956UNIONALLSELECT1945,'0x025d0',9680
UNIONALLSELECT1946,'0x092d0',37584UNIONALLSELECT1947,'0x0d2b2',53938
UNIONALLSELECT1948,'0x0a950',43344UNIONALLSELECT1949,'0x0b557',46423
UNIONALLSELECT1950,'0x06ca0',27808UNIONALLSELECT1951,'0x0b550',46416
UNIONALLSELECT1952,'0x15355',86869UNIONALLSELECT1953,'0x04da0',19872
UNIONALLSELECT1954,'0x0a5d0',42448UNIONALLSELECT1955,'0x14573',83315
UNIONALLSELECT1956,'0x052d0',21200UNIONALLSELECT1957,'0x0a9a8',43432
UNIONALLSELECT1958,'0x0e950',59728UNIONALLSELECT1959,'0x06aa0',27296
UNIONALLSELECT1960,'0x0aea6',44710UNIONALLSELECT1961,'0x0ab50',43856
UNIONALLSELECT1962,'0x04b60',19296UNIONALLSELECT1963,'0x0aae4',43748
UNIONALLSELECT1964,'0x0a570',42352UNIONALLSELECT1965,'0x05260',21088
UNIONALLSELECT1966,'0x0f263',62051UNIONALLSELECT1967,'0x0d950',55632
UNIONALLSELECT1968,'0x05b57',23383UNIONALLSELECT1969,'0x056a0',22176
UNIONALLSELECT1970,'0x096d0',38608UNIONALLSELECT1971,'0x04dd5',19925
UNIONALLSELECT1972,'0x04ad0',19152UNIONALLSELECT1973,'0x0a4d0',42192
UNIONALLSELECT1974,'0x0d4d4',54484UNIONALLSELECT1975,'0x0d250',53840
UNIONALLSELECT1976,'0x0d558',54616UNIONALLSELECT1977,'0x0b540',46400
UNIONALLSELECT1978,'0x0b5a0',46496UNIONALLSELECT1979,'0x195a6',103846
UNIONALLSELECT1980,'0x095b0',38320UNIONALLSELECT1981,'0x049b0',18864
UNIONALLSELECT1982,'0x0a974',43380UNIONALLSELECT1983,'0x0a4b0',42160
UNIONALLSELECT1984,'0x0b27a',45690UNIONALLSELECT1985,'0x06a50',27216
UNIONALLSELECT1986,'0x06d40',27968UNIONALLSELECT1987,'0x0af46',44870
UNIONALLSELECT1988,'0x0ab60',43872UNIONALLSELECT1989,'0x09570',38256
UNIONALLSELECT1990,'0x04af5',19189UNIONALLSELECT1991,'0x04970',18800
UNIONALLSELECT1992,'0x064b0',25776UNIONALLSELECT1993,'0x074a3',29859
UNIONALLSELECT1994,'0x0ea50',59984UNIONALLSELECT1995,'0x06b58',27480
UNIONALLSELECT1996,'0x055c0',21952UNIONALLSELECT1997,'0x0ab60',43872
UNIONALLSELECT1998,'0x096d5',38613UNIONALLSELECT1999,'0x092e0',37600
UNIONALLSELECT2000,'0x0c960',51552UNIONALLSELECT2001,'0x0d954',55636
UNIONALLSELECT2002,'0x0d4a0',54432UNIONALLSELECT2003,'0x0da50',55888
UNIONALLSELECT2004,'0x07552',30034UNIONALLSELECT2005,'0x056a0',22176
UNIONALLSELECT2006,'0x0abb7',43959UNIONALLSELECT2007,'0x025d0',9680
UNIONALLSELECT2008,'0x092d0',37584UNIONALLSELECT2009,'0x0cab5',51893
UNIONALLSELECT2010,'0x0a950',43344UNIONALLSELECT2011,'0x0b4a0',46240
UNIONALLSELECT2012,'0x0baa4',47780UNIONALLSELECT2013,'0x0ad50',44368
UNIONALLSELECT2014,'0x055d9',21977UNIONALLSELECT2015,'0x04ba0',19360
UNIONALLSELECT2016,'0x0a5b0',42416UNIONALLSELECT2017,'0x15176',86390
UNIONALLSELECT2018,'0x052b0',21168UNIONALLSELECT2019,'0x0a930',43312
UNIONALLSELECT2020,'0x07954',31060UNIONALLSELECT2021,'0x06aa0',27296
UNIONALLSELECT2022,'0x0ad50',44368UNIONALLSELECT2023,'0x05b52',23378
UNIONALLSELECT2024,'0x04b60',19296UNIONALLSELECT2025,'0x0a6e6',42726
UNIONALLSELECT2026,'0x0a4e0',42208UNIONALLSELECT2027,'0x0d260',53856
UNIONALLSELECT2028,'0x0ea65',60005UNIONALLSELECT2029,'0x0d530',54576
UNIONALLSELECT2030,'0x05aa0',23200UNIONALLSELECT2031,'0x076a3',30371
UNIONALLSELECT2032,'0x096d0',38608UNIONALLSELECT2033,'0x04bd7',19415
UNIONALLSELECT2034,'0x04ad0',19152UNIONALLSELECT2035,'0x0a4d0',42192
UNIONALLSELECT2036,'0x1d0b6',118966UNIONALLSELECT2037,'0x0d250',53840
UNIONALLSELECT2038,'0x0d520',54560UNIONALLSELECT2039,'0x0dd45',56645
UNIONALLSELECT2040,'0x0b5a0',46496UNIONALLSELECT2041,'0x056d0',22224
UNIONALLSELECT2042,'0x055b2',21938UNIONALLSELECT2043,'0x049b0',18864
UNIONALLSELECT2044,'0x0a577',42359UNIONALLSELECT2045,'0x0a4b0',42160
UNIONALLSELECT2046,'0x0aa50',43600UNIONALLSELECT2047,'0x1b255',111189
UNIONALLSELECT2048,'0x06d20',27936UNIONALLSELECT2049,'0x0ada0',44448
3.创建存储过程：
.转换阿拉伯数字为汉字数字
CREATEFUNCTION[dbo].[f_num_str](@numint)
RETURNSvarchar(100)
AS
BEGIN
(20),@reVARCHAR(20),@iint
=cast(@numasvarchar),@i=1,@re=''
<=len(@n_str)
BEGIN
=@re+SUBSTRING('零一二三四五六七八九',CAST(SUBSTRING(@n_str,@i,1)ASint)+1,1)
=@i+1
END
END
.根据阳历返回对应的阴历
CREATEPROCEDUREGetLunar_zhangzs
@solarDayDATETIME--日期
AS
(10)
--保证传进来的日期是不带时间
=cast(@solarDayASchar(10))
=CAST(@solarDay-'1900-01-30'ASINT)
--确定农历年开始
=1900
=@solData
<>0
BEGIN
=348
=0
--根据年 id获得对应的 datInt值
=dataIntFROMSolarDataWHEREyearId=@i
--传回农历年的总天数
=32768
>8
BEGIN
&@j>0
=@yDays+1
=@j/2
END
--传回农历年闰哪个月 1-12,没闰传回 0
=@iLunar&15
--传回农历年闰月的天数,加在年的总天数上
>0
BEGIN
&65536>0
=30
ELSE
=29
=@yDays+@mLeapNum
END
=@
=@i+1
END
<=0
BEGIN
=@offset+@yDays
=@i-1
END
--确定农历年结束
=@i
--确定农历月开始
=1
=dataIntFROMSolarDataWHEREyearId=@YEAR
--判断那个月是润月
=@iLunar&15
=0
<>0
BEGIN
--判断润月
=0
IF(@mLeap>=(@mLeap+1)=0)
BEGIN--是润月
=@i-1
=1
--传回农历年闰月的天数
&65536>0
=30
ELSE
=29
END
ELSE
--不是润月
BEGIN
=1
=65536
<=@i
BEGIN
=@temp/2
=@j+1
END
&@temp>0
=30
ELSE
=29
END
--解除闰月
==(@mLeap+1)
=0
=@
=@i+1
END
<=0
BEGIN
=@offset+@mDays
=@i-1
END
--确定农历月结束
=@i
--确定农历日结束
=@offset
=convert(varchar(10),CAST((CAST(@YEARASVARCHAR(4))+'-'+CAST(@MONTHASVARCHAR(2))+'-'+CAST(@DAY
ASVARCHAR(2)))ASDATETIME),120)
selectconvert(varchar(10),@solarDay,120)as阳历
,cast(_num_str(year(@OUTPUTDATE))asvarchar(8))+'年|'+
casewhendatalength(_num_str(month(@OUTPUTDATE)))=4thencasewhenleft(_num_str(month(@
OUTPUTDATE)),1)<>'一'
thenleft(month(@OUTPUTDATE),1)else''end+' 十
'+casewhenright(_num_str(month(@OUTPUTDATE)),1)=' 零
'then''elseright(_num_str(month(@OUTPUTDATE)),1)endelse
cast(_num_str(month(@OUTPUTDATE))asvarchar(4))end+'月|'
+casewhendatalength(_num_str(day(@OUTPUTDATE)))=4thencasewhencast(_num_str(day(@OUT
PUTDATE))asvarchar(4))=' 一 零 'then' 初
'else(casewhenleft(cast(_num_str(day(@OUTPUTDATE))asvarchar(4)),1)<>'一'
thenleft(cast(_num_str(day(@OUTPUTDATE))asvarchar(4)),1)else''end)end+' 十
'+casewhenright(cast(_num_str(day(@OUTPUTDATE))asvarchar(4)),1)='零'then''else
right(cast(_num_str(day(@OUTPUTDATE))asvarchar(4)),1)endelse' 初
'+cast(_num_str(day(@OUTPUTDATE))asvarchar(4))endas阴历
GO
--调用存储过程
execGetLunar_zhangzs'2010-02-28'
--返回结果：
阳历阴历
------------------------------------
2010-02-28二零一零年|一月|十五--正月十五(元宵节)
干支计算方法
1.干支相关类型定义：
CREATEORREPLACETypegzTypeasobject
(
idnumber(3),
gznchar(2)
)
CREATEORREPLACETypegzTableTypeastableofgzType
2.干支计算方法 1：
createorreplacefunctiongetGzData
returngzTableTypePIPELINED--将管道与一个 table对象 MsgType关联
as
TypemyTgisvarray(10)ofnchar(1);
TypemyDzisvarray(12)ofnchar(1);
tgmyTg:=myTg('甲','乙','丙','丁','戊','己','庚','辛','壬','癸');
dzmyDz:=myDz('子','丑','寅','卯','辰','巳','午','未','申','酉','戌','亥');
indexTginteger:=0;
indexDzinteger:=0;
tnchar(2);
begin
foriin1..60loop
t:=tg(indexTg+1)||dz(indexDz+1);
piperow(gzType(i,t));
indexTg:=mod((indexTg+1),10);--调整天干索引
indexDz:=mod((indexDz+1),12);--调整地支索引
endloop;
return;--必须返回'空'
end;
3.干支计算方法 2：
createorreplacefunctiongetGzData2
returngzTableType
as
TypemyTgisvarray(10)ofnchar(1);
TypemyDzisvarray(12)ofnchar(1);
tgmyTg:=myTg('甲','乙','丙','丁','戊','己','庚','辛','壬','癸');
dzmyDz:=myDz('子','丑','寅','卯','辰','巳','午','未','申','酉','戌','亥');
indexTginteger:=0;
indexDzinteger:=0;
tnchar(2);
v_resultgzTableType:=gzTableType();--实例化对象
begin
foriin1..60loop
t:=tg(indexTg+1)||dz(indexDz+1);
--piperow(gzType(i,t));
--增加的元素赋值，如果没用 EXTEND，这里会出错
v_();--增加一个元素
v_result(v_):=gzType(i,t);--为新增加的元素赋值
indexTg:=mod((indexTg+1),10);--调整天干索引
indexDz:=mod((indexDz+1),12);--调整地支索引
endloop;
returnv_result;--必须返回
end;
oracle10g实现把汉字转换成汉语拼音首字母
第一步、创建 java源
createorreplaceandresolvejavasourcenamed"hz2py"
aspublicclasshz2py{
publicstaticintpare(stringstr1,stringstr2)
{
intresult=0;
stringm_s1=null;
stringm_s2=null;
try
{
m_s1=newstring((_fromencode_),_toencode_);
m_s2=newstring((_fromencode_),_toencode_);
}
catch(exceptione)
{
(str2);
}
result=chinesepareto(m_s1,m_s2);
returnresult;
}
publicstaticintgetcharcode(strings)
{
if(s==null&&(""))
return-1;
byteb[]=();
intvalue=0;
for(inti=0;i<&&i<=2;i++)
value=value*100+b[i];
returnvalue;
}
publicstaticintchinesepareto(strings1,strings2)
{
intlen1=();
intlen2=();
intn=(len1,len2);
for(inti=0;i<n;i++)
{
ints1_code=getcharcode((i)+"");
ints2_code=getcharcode((i)+"");
if(s1_code*s2_code<0)
return(s1_code,s2_code);
if(s1_code!=s2_code)
returns1_code-s2_code;
}
returnlen1-len2;
}
publicstaticstringgetbegincharacter(stringres)
{
stringa=res;
stringresult="";
for(inti=0;i<();i++)
{
stringcurrent=(i,i+1);
if(pare(current,"\u554a")<0)
result=result+current;
else
if(pare(current,"\u554a")>=0&&pare(current,"\u5ea7")<=0)
if(pare(current,"\u531d")>=0)
result=result+"z";
else
if(pare(current,"\u538b")>=0)
result=result+"y";
else
if(pare(current,"\u6614")>=0)
result=result+"x";
else
if(pare(current,"\u6316")>=0)
result=result+"w";
else
if(pare(current,"\u584c")>=0)
result=result+"t";
else
if(pare(current,"\u6492")>=0)
result=result+"s";
else
if(pare(current,"\u7136")>=0)
result=result+"r";
else
if(pare(current,"\u671f")>=0)
result=result+"q";
else
if(pare(current,"\u556a")>=0)
result=result+"p";
else
if(pare(current,"\u54e6")>=0)
result=result+"o";
else
if(pare(current,"\u62ff")>=0)
result=result+"n";
else
if(pare(current,"\u5988")>=0)
result=result+"m";
else
if(pare(current,"\u5783")>=0)
result=result+"l";
else
if(pare(current,"\u5580")>=0)
result=result+"k";
else
if(pare(current,"\u51fb")>0)
result=result+"j";
else
if(pare(current,"\u54c8")>=0)
result=result+"h";
else
if(pare(current,"\u5676")>=0)
result=result+"g";
else
if(pare(current,"\u53d1")>=0)
result=result+"f";
else
if(pare(current,"\u86fe")>=0)
result=result+"e";
else
if(pare(current,"\u642d")>=0)
result=result+"d";
else
if(pare(current,"\u64e6")>=0)
result=result+"c";
else
if(pare(current,"\u82ad")>=0)
result=result+"b";
else
if(pare(current,"\u554a")>=0)
result=result+"a";
}
returnresult;
}
privatestaticstring_fromencode_="gbk";
privatestaticstring_toencode_="gbk";
}
/
第二步、创建函数使用 java类
createorreplace
functionf_hz2py(namevarchar2)returnvarchar2
aslanguagejavaname
'hz2(.string)';
/
selectf_hz2py('美国鬼子，小日本')fromdual;
返回 mggz，xrb
以上给出了扩展 oracle自身提供功能的一种方法。同行们可以把 java类转化为函数函数，供 pl/sql调用。
java源被 oracle8i以上版本支持。oracle8i以上版本，可以把 java源看作 view、proc、table等对 oracle
对象一样
Oracle10g中利用哈希函数提高查询速度
2007-04-0214:27bensiyuan赛迪网技术社区我要评论(0)
摘要：本文通过一个信用卡业务的数据库来介绍如何建立建立一个簇表，使用一个哈希函数来约束
簇块的数量，以及如何在进行每个查询操作时使用排序哈希簇。
标签：哈希函数簇 Oracle10g
Oracle帮您准确洞察各个物流环节
当数据存储在一个普通表中的时候，这些记录将以插入到数据库时的顺序物理地保存到分配
的块中。例如，如果有一个用于存储员工信息的表，那么员工姓名将会按照插入到表的顺序
存储在表中。
如果员工记录非常多的话，那么数据表的响应速度就会逐渐变慢。你可以通过选择值相对等
分布的一列（如员工的部门编号）并建立一个簇表来提高查询员工的速度。
在簇表中，如果员工属于同一个部门，那么它们的记录将物理地存储在同一系列的块中。这
样就可以提高查找员工信息的速度，这是因为在检索某个特定部门的员工时，需要读取数据
库块的数量减少了。而在非簇表中查找员工，就可能需要对每个数据库块进行访问。
当表中存在大量键值的时候，你就会开始发现由于存在许多簇块而导致的性能问题。避免这
个问题的一个方法就是使用一个哈希函数来约束簇块的数量。哈希函数将会给定一个数值用
来限定簇块数量的预计范围，但它得到的值是相对等分布的。例如你可以创建一个哈希函数，
只比较部门编号的最后两位。
哈希函数中存在的一个问题就是函数值会打乱记录原本的顺序。你可以通过 ORDERBY来解决
这个问题；但是，在很多情况下，记录数量是非常庞大的。在 Oracle10g中，你可以将一个
数据定义为“naturalorder”，那么就可以不用经过排序而以你所希望的顺序来检索哈希簇
的数据，从而解决了上面的提出问题。
例如，假设你有一个信用卡业务的数据库。你决定以信用卡号作为簇主键将有利于数据的存
储分布。但是，由于存在大量的信用卡号，所以可以使用一个哈希函数来约束簇块的数量。
而且你希望在你的大部分报表中数据是按照时间顺序排列的，那么在进行每个查询操作时使
用排序哈希簇，而不要使用 ORDERBY。下面给出了相关语句：
createclustercredit_cluster
(
card_novarchar2(16),
transdatedatesort
)
hashkeys10000hashisora_hash(card_no)
size256;
createtablecredit_orders
(
card_novarchar2(16),
transdatedate,
amountnumber
)
clustercredit_cluster(card_no,transdate);
altersessionsetnls_date_format="YYYYMMDDHH24MISS";
insertintocredit_orders(card_no,transdate,amount)
values('','123',);
insertintocredit_orders(card_no,transdate,amount)
values('','216',);
insertintocredit_orders(card_no,transdate,amount)
values('','111',);
insertintocredit_orders(card_no,transdate,amount)
values('','001',);
可以看到我在这里使用了一个新函数 ORA_HASH 来为信用卡建立一个哈希数值。现在，你可
以非常简单地对某个信用卡数据进行查询，并返回自动排序后的结果。
