Oracle SQL：select各类查询语句总结

SQL查询语句总结

数据查询语言：即DML(Data Mannipulation Language)数据操纵语言，用于查询、操纵数据表资料行

本手册所有示例引用表均以TIPTOP GP ERP数据库表为基础演示，

TIPTOP 数据字典下载地址：http://pan.baidu.com/s/1jG1PGF0

1、SELECT 查询所有列资料或特定列资料








--查询订单产品,产品品名资料







SELECT oeb04,oeb06 FROM oeb_file;

OEB04 OEB06

-------------- ------------

J00020853 F3D111-15

J00020886 F2A036-25

J00020901 F2A046b10-GLD

-----------------------------------------------------------------------------------------------------------------------------------------

2、distinct消除查询结果的重复行








--查询订单产品,产品品名资料，且结果消除重复行(请对比SQL示例1)







SELECT DISTINCT oeb04,oeb06 FROM oeb_file;

OEB04 OEB06

-------------- ------------

J00020853 F3D111-15

J00020886 F2A036-25

J00020901 F2A046b10-GLD

-----------------------------------------------------------------------------------------------------------------------------------------

3、在SELECT查询资料列上使用算术表达式(+、-、*、/)及as应用使用列别名








--as指定列别名(列别名也可以不使用as,直接在查询列后面指定即可)，及SQL中使用乘法运算*







SELECT oeb01 as "订单单号", oeb04 as "订单产品",oeb06 as "产品品名",oeb12*oeb13 as "订单金额"







 FROM oeb_file

订单单号订单产品产品品名订单金额

ZE1-11070001    J01960002    USB 2.0成品                          288
ZE1-11070002    J00130953    AIC-BF-3300BK-500-J          10000
ZE1-11070007    J01960001    USB2.0 CABLE                     62500

注意：如果取了别名,此别名是不能在WHERE 子句中引用的,必须使用原运算式或内嵌表解决

-----------------------------------------------------------------------------------------------------------------------------------------

4、nvl函数或nvl2函数处理算术表达式运算中栏位空值问题

如果查询的栏位参与+ - / *算术运算，只要参与运算的栏位有一个为空值，则会导致整个运算结果为空值

nvl(expr1,expr2)如果expr1不为空，则返回expr1，否则返回expr2；expr1与expr2可以是任意数据类型,但是expr1与expr2需是相同的数据类型

nvl(expr1,expr2,expr3)如果expr1不为空，则返回expr2，否则返回expr3；expr1可以是任意数据类型,但是expr2与expr3需是相同的数据类型








--冲销数量ta_oeb013栏位有空值现象 ，ta_oeb013为本公司客制栏位(冲销数量)







SELECT oeb01 as "订单单号", oeb04 as "订单产品",oeb06  as "品名",oeb12-oeb24-ta_oeb013  as "订单未交量"  FROM oeb_file

订单单号订单产品品名订单未交量

----------------- -------------- -------------------------------- -------------

ZC1-11040001 J00020430 A3X126x50-YLW-M
ZC1-11050001 ABCDEFGH 插头 10








-- 用nvl函数处理空值问题 ，ta-oeb013 为本司客制栏位冲销数量，值为空







SELECT oeb01 as "订单单号", oeb04 as "订单产品",oeb06  as "品名",oeb12-oeb24-nvl(ta_oeb013,0) as "订单未交量"  FROM oeb_file  







--或







-- 用nvl2函数处理空值问题 ，ta-oeb013 为本司客制栏位冲销数量，值为空







SELECT oeb01 as "订单单号", oeb04 as "订单产品",oeb06  as "品名",







       nvl2(ta_oeb013,oeb12-oeb24-ta_oeb013,oeb12-oeb24) as "订单未交量"  FROM oeb_file

订单单号订单产品品名订单未交量

----------------- -------------- -------------------------------- -------------

ZC1-11040001 J00020430 A3X126x50-YLW-M 100
ZC1-11050001 ABCDEFGH 插头 10

-----------------------------------------------------------------------------------------------------------------------------------------

5、使用WHERE条件子句

常用的WHERE条件如下：

等于：= 不等于：<>，!= 大于：>

大于或等于：>= 小于或等于：<= 小于：<

匹配于列表值：IN( )

在两值之间：BETWEEN .. AND ..

测试是否为NULL：IS NULL (注意："没有空格"、"一个或多个空格" 与 IS NULL 是不同的)

匹配于字符样式：LIKE，执行模糊查询需要使用LIKE,常用LIKE通配符如下：

%：匹配0个或多个字符

_ ：匹配单个字符

如果模糊查询本身要查的栏位资料里面包含有'%'或'_'字符时，则需要使用转义字符ESCAPE

注意：如果某值是空则并不能用LIKE "%"匹配的到，写SQL查异常这点经常出错

常用的WHERE条件逻辑操作符如下：

逻辑与：AND，逻辑或：OR，取反：NOT








--查询Tiptop GP用户账号中带有'_'下划线的用户信息，escape用'a'做转义字符且2011/03月期间开通的账号







SELECT * FROM zx_file  







  WHERE zx03 = '2G69' AND zx01 LIKE '%a_%' ESCAPE 'a'    







    AND zxdate BETWEEN to_date('2011/03/01','YYYY/MM/DD') AND to_date('2011/03/31','YYYY/MM/DD')

ZX01 ZX02 ZX03 ZX04

--------- ---------- ------ ----------
top_test 測試帳號 2G69 CLASS-A

-----------------------------------------------------------------------------------------------------------------------------------------

6、ORDER BY desc (升序排序:默认)，ORDER BY asc (降序排序) 将查询出来的资料排序

排序时也可以指定多列排序、非选择资料栏位排序、别名排序、列位置编号排序








SELECT zx01,zx02 as "姓名",zx03,zx04 FROM zx_file  







 WHERE zx03 = '2G69' or zx03 = '2G60'  







 ORDER BY zx01 asc,"姓名",3 asc,zx09 desc  --不同列排序必须分别指定排序规则

ZX01 姓名 ZX03 ZX04
--------- ----------- -------- ----------

cmtest     C-M-测试   2G60        CLASS-B
tiptop      tiptop         2G69
top_test    测试账号      2G60        CLASS-A
toptest     测试账号      2G69        CLASS-C

-----------------------------------------------------------------------------------------------------------------------------------------

7、连接查询：基于2个或2个以上的视图或表的查询

说明：必须在FROM子句后接上2个或2个以上的表或视图

查询的资料列如果在FRM子句后面所捷的表或视图有多个,则必须在查询的资料栏位前面加上表或视图名作为首码,否则会引起2意性错误

当使用连接查询时必须在WHERE子句中指定连接的条件,否则会产生笛卡尔积(X*Y)

使用别名可以简化SQL的编写,别名必须跟在表或视图的后面

7.1、简单连接查询：FROM子句接表或视图，WHERE子句指定连接条件








----查询tiptop GP系统用户帐号和部门信息







SELECT zx_file.zx01,zx_file.zx02,zx_file.zx03,gem_file.gem02   







  FROM zx_file,gem_file  







 WHERE zx_file.zx03 = gem_file.gem01  







--等同于







----查询tiptop GP系统用户帐号和部门信息







SELECT zx01,zx02,zx03,gem02   







  FROM zx_file,gem_file  







 WHERE zx03 = gem01

ZX01 ZX02 ZX03 GEM02

------- ------- ----- ---------
terry 李帅 2G69 海外资讯
zhanna 詹妹 2G89 会计部

7.2、内连接INNER JOIN、左外连接LEFT JOIN、右外连接RIGHT JOIN、完全外连接FULL JOIN、+操作符

内连接INNER JOIN：用于返回满足条件的所有记录,默认情况下在执行查询如果没有指定任何连接操作,则此查询SQL即为内连接








--查询tiptop系统用戶信息







--Oracle 9i 之前的语法,连接条件在WHERE子句中







SELECT zx01 as "用户ID",zx02 as "用户姓名",zx03 as "部门ID",gem02 as "部门简称"   







  FROM zx_file,gem_file  







 WHERE zx03 = gem01  







--等同于







--Oracle 9i 之后的语法法，使用join或是inner join，连接条件写在on子句中，join子句指定连接的表或视图   







SELECT zx01 as "用户ID",zx02 as "用户姓名",zx03 as "部门ID",gem02 as "部门简称"   







 FROM zx_file 







 JOIN gem_file ON zx03 = gem01 --或使用inner join

用户ID 用户姓名部门ID 部门简称
-------- --------- -------- ----------

terry 李帅 2G69 海外资讯
zhanna 詹妹 2G89 会计部

左外连接LEFT JOIN/LEFT OUTER JOIN：不仅返回满足连接条件的结果集而且返回不满足连接条件的但是位于连接操作符左边表或视图的结果集








SELECT zx01,zx02,zx03,gem02 FROM zx_file  







  LEFT JOIN gem_file ON zx03 = gem01  --或 left outer join  







--等同于如下使用+操作符的SQL语句







SELECT zx01,zx02,zx03,gem02   







  FROM zx_file,gem_file 







 WHERE zx03 = gem01(+) --使用+操作符进行左外连接 等同 left join  .. on .. 注意+的位置

--例如 qintan 用户的部门编号不存在gem_file，chris 用户部门信息为空
ZX01 ZX02 ZX03 GEM02

---------    ------     ------    ----------
terry       李某 2G69    海外资讯
zhanna    詹妹      2G89    海外会计
qintan     情 2M30
chris        chris

右外连接RIGHT JOIN/RIGHT OUTER JOIN：不仅返回满足连接条件的结果集而且返回不满足连接条件的但是位于连接操作符右边表或视图的结果集








SELECT zx01,zx02,zx03,gem02 FROM zx_file  







 RIGHT JOIN gem_file on zx03 = gem01  --或 right outer join  







--等同于如下使用 + 操作符的SQL语句







SELECT zx01,zx02,zx03,gem02   







  FROM zx_file,gem_file  







 WHERE zx03(+) = gem01    --使用+操作符进行右外连接 等同 right join .. on .. 注意+的位置

ZX01 ZX02 ZX03 GEM02

-------- ------ ------ ---------
terry 李某 2G69 海外资讯
zhanna 詹妹 2G89 海外会计
行政部
企划科

完全外连接FULL JOIN/FULL OUTER JOIN：不仅返回满足连接条件的结果集而且返回不满足连接条件的位于连接操作符左右边表或视图的结果集








SELECT zx01,zx02,zx03,gem02 FROM zx_file  







 FULL JOIN gem_file ON zx03 = gem01  --或 full outer join

ZX01 ZX02 ZX03 GEM02

--------- ------ ------ ---------
terry 李某   2G69 海外资讯
zhanna  詹妹    2G89    海外会计
qintan 情        2M30
chris      christ
行政部
企划科

7.3、+操作符：Oracle 9i之前的语法，现在已经不建议使用了，+出现在列的哪一边则哪一边的数据可以不一定要存在

+操作符只能够出现在where子句中，且不能同时跟OUTER JOIN语法一起使用

+操作符只能够实现左外连接(LEFT JOIN)及右外连接(RIGHT JOIN)，但是不能够实现完全外连接

+操作符只能使用在列上，不能够使用在运算式上

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

8、自连接查询：同一张表之间的连接查询，主要用在自参照表上显示同一张表不同栏位列的关系

-----------------------------------------------------------------------------------------------------------------------------------------

9、子查询：也称嵌套查询，是指嵌入在其他SQL语句中的SELECT语句

注意：当在DDL语句中引用子查询时可以带有ORDER BY子句,但是当在WHERE子句、SET子句中引用子查询时则不可以带有ORDER BY子句

应用：通过在WHERE、HAVING、START WITH子句中使用子查询可以提供条件值

通过在UPDATE语句中使用子查询可以修改一列或多列数据

通过在INSERT或CREATE TABLE语句中使用子查询可以将来源表数据插入到目标表中

通过在CREATE VIEW或是CREATE MATERIALIZED VIEW中使用子查询可以定义视图

9.1、单行子查询：只返回一行数据的子查询








--查询部门简称为"ERP"的所有账号信息







SELECT zx01,zx02,zx03 FROM zx_file  







 WHERE zx03 = (SELECT gem01 FROM gem_file WHERE gem02 = 'ERP')

ZX01 ZX02 ZX03

--------       ----------   ------
toptest      测试账号   2G69
terry          李某         2G69

9.2、多行子查询：只返回多行数据的子查询

当在WHERE子句中使用多行子查询时,必须使用多行比较符IN、ALL、ANY

IN：匹配子查询结果中的任意一个值即可

ALL：必须要符合子查询结果中的所有值, 不能单独使用,需配合单行比较符(=,>,<.>=,<=,<>,!=)使用

ANY：只要符号子查询结果中的任意值即可,不能单独使用,需配合单行比较符(=,>,<.>=,<=,<>,!=)使用

多行子查询中使用IN操作符








--查询部门别存在部门信息gem_file中且部门编号是2G开头的人员账号信息







SELECT zx01,zx02,zx03 FROM zx_file  







 WHERE zx03 IN (SELECT gem01 FROM gem_file WHERE gem01 LIKE '2G%')

ZX01 ZX02 ZX03

--------    ---------    ------
toptest   测试账号 2G69
terry       李某          2G69
zhanna 詹妹         2G89
pIG         XYZ           2G80

多行子查询中使用ALL操作符








--查询内销订单中接单金额>所有外销订单金额的订单明细 







SELECT oea01 as "订单",oeb04 as "产品",oeb12*oeb13 as "金额" FROM oeb_file,oea_file  







  WHERE oea01= oeb01 







    AND oea08 = '1' 







    AND (oeb12*oeb13 > ALL (







                             SELECT oeb12*oeb13 FROM oeb_file   







                               WHERE oea01 = oeb01 AND oea08 = '2'  







                           )







        )

订单产品金额

-----------------     ----------------------------         ---------
ZX1-11100659   12P0016W302111173             34000
ZX1-11100658    12P0010211-00111                 14460
ZX1-11120228    35PT021120-001554               16080
ZX1-12010058    12P6000CW30201173             81000

多行子查询中使用ANY操作符








--查询接单金额等于任意一个以ZE1-1106开头的订单的接单金额的订单明细  







SELECT oeb01 as "订单",oeb04 as "产品",oeb12*oeb13 as "金额" FROM oeb_file  







  WHERE oeb12*oeb13 = ANY(







                          SELECT oeb12*oeb13 FROM oeb_file WHERE oeb01 like 'ZE1-1108%'







                         )

订单产品金额

-----------------     ----------------------------         ---------
ZZ1-13100639   15P001XXXX02111173           78000
ZZ1-13100698    12P58912211-00111              88000

9.3、多列子查询：多列数据必须同时匹配

区别单行子查询、多行子查询、多列子查询

单行子查询：返回单列单行数据，针对单列

多行子查询：返回单列多行数据，针对单列

多列子查询：返回多列数据的子查询，针对多列

可以返回多列单行数据，此时WHERE子句可以使用单行比较符如=.....，查询返回多列成对匹配的结果

可以返回多列多行数据，此时WHERE子句可以使用多行比较符如IN.....，查询返回多列成对匹配的结果








--查询未转工单的订单+项次  







SELECT oeb01 as "未转工单订单",oeb03 as "订单项次",oeb04 as "产品",oeb06 as "品名"  







   FROM oeb_file  







  WHERE (oeb01,oeb03) NOT IN (select sfb22,sfb221 FROM sfb_file) --此处(oeb01,oeb03)会与sfb22,sfb221成对匹配

未转工单订单订单项次产品品名

-----------------   ----------      ---------------------- -    -----------------------------------
SY1-11080005   1                 WZ110309990GCD     DVI 18+1 To DVI 18+1 黑色
SY1-11080006   1                 WZ110309930GCD     HDD 15M To HDD 15M黑色
SY1-11080007   1                 W11031010-0G          D-SUB 15P M to D-SUB 15P M

9.4、在FROM子句中使用子查询：FROM子句中使用的子查询，该子查询会被当做视图看待，也称内嵌视图








--查询2013年3月 薪资高于或等于本部薪资平均水平的人员  







SELECT cqk_file.cqk05 as "部门编号",cqk09 as "员工姓名",cqk30 as "薪资",avgsalary as "部门均资" 







 FROM cqk_file, 







               (SELECT cqk05,avg(cqk30) avgsalary FROM cqk_file  







                  WHERE cqk03 = 2012 and cqk031 = 3  







                GROUP BY cqk05







                )avg_salary  







  WHERE cqk_file.cqk05 = avg_salary.cqk05  







    AND cqk_file.cqk30 >= avg_salary.avgsalary  







    AND cqk03 = 2013 AND cqk031 = 3

部门编号员工姓名薪资部门薪资

---------    ----------               ----------------        ---------
2SF0        刘SIR                    77884.000000      46127
2N10       黄SIR                    56621.000000      22816
2B22        陈SIR                   86197.000000     86197

9.5、相关子查询EXISTS

相关子查询是指需要应用主查询列表中的子查询语句,需要通过谓词EXISTS来实现

当使用EXSISTS谓词时,如果子查询存在返回结果则条件返回TRUE,否则返回FALSE








--查询部门存在于部门别gem_file中且部门编号为2G69的tiptop系统用户 







SELECT zx01,zx02,zx03 FROM zx_file   







  WHERE EXISTS                             







  (  







     SELECT 1 FROM gem_file WHERE zx_file.zx03 = gem_file.gem01 AND gem01 = '2G69'  







  ) --使用EXISTS,因为gem_file有2G69部门资讯,所以子查询返回的条件为true

ZX01 ZX02 ZX03

--------    -----------     ------
cmtest    C-M-TEST 2G69
terry       李某             2G69

-----------------------------------------------------------------------------------------------------------------------------------------

10、合并查询：为了合并多个查询结果可以使用UNION、UNION ALL、INTERSECT、MINUS

语法：SELECT 语句1 [UNION |UNION ALL |INTERSECT | MINUS] SELECT 语句2

说明：这些集合操作符具有相同的优先级,当同时使用多个操作符时会按照从左到右的方式应用这些集合操作符

使用集合操作符时必须确保不同的查询语句查询的列的个数相同且类型也必须匹配

子句不能有ORDER、GROUP，必须放在整个语句的末尾

UNION：获取两个结果的并集，且去掉合并结果中重复的记录








--UNION应用：查询tiptop系统用户名以ta开头和部门编号是2G69的所有用户







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx01 LIKE 'te%'







 UNION







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx03 = '2G69'

ZX01 ZX02 ZX03

--------     -----------     ------
cmtest     C-M-测试    2G69
terry        李某             2G69
tiptop                         2G69
toptest    测试账号      2G69

UNION ALL：获取两个结果的并集，但不会去掉合并结果中重复的记录








--UNION ALL应用：查询tiptop系统用户名以ta开头和部门编号是2G69的所有用户







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx01 LIKE 'te%'







 UNION ALL  --注意查询结果与UNION的区别







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx03 = '2G69'

ZX01 ZX02 ZX03

--------     -----------     ------
cmtest     C-M-测试    2G69
terry        李某             2G69

terry        李某             2G69
tiptop                         2G69
toptest    测试账号      2G69

INTERSECT：取两个结果的交集








--INTERSECT应用：查询tiptop系统用户名以ta开头且部门编号是2G69的所有用户







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx01 LIKE 'te%'







 INTERSECT







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx03 = '2G69'

ZX01 ZX02 ZX03

-------- ----------- ------
terry 李某 2G69

MINUS：获取两个结果集的差集，返回在第一个结果集中存在但是第二个结果集中不存在的记录








--MINUS应用：注意查询结果和上述的差别







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx01 LIKE 'te%'







 MINUS







SELECT zx01,zx02,zx03 FROM zx_file WHERE zx03 = '2G69'

ZX01 ZX02 ZX03

--------     -----------     ------
cmtest     C-M-测试    2G69
tiptop                         2G69
toptest    测试账号      2G69

-----------------------------------------------------------------------------------------------------------------------------------------

11、数据分组查询统计

实际应用中常需要做的就统计数据库中的信息,按照指定列分组群以比较各组群的差异信息

Oracle数据分组通过使用GROUP BY子句指定要分组的列

使用分组函数如COUNT、AVG、SUM、MAX、MIN等显示统计结果

使用HAVING子句限制分组显示结果(注意:having后面必须接分组函数)

如果查询有分组函数计算,则先将WHERE条件符合的资料计算,HAVING子句只是显示分组显示的结果,不参与分组函数的计算

常用分组函数：

MAX：取得列或运算式的最大值,适用于任何数据类型 MIN：取得列或运算式的最小值,适用于任何数据类型

AVG：取得列或运算式的平均值,适用于任何数据类型 SUM：取得列或运算式的总和，只适用于数字类型

COUNT：取得行数

VARIANCE：取得列或运算式的方差,只适用于数字类型 STDDEV：取得列或运算式的偏差,只适用于数字类型

注意：分组函数只能够出现在选择列表、ORDER BY、HAVING子句中，不能出现在 WHERE和GROUP BY子句中

除了COUNT(*)之外,其他分组函数都会忽略NULL行

执行SELECT语句时，选择列必须出现在GROUP BY子句中








--查询所有部门不同年月中的部门最低、平均、最高薪资，且部门当年月份领薪人员数>=3个  







SELECT cqk05,cqk03,cqk031,MIN(cqk30),AVG(cqk30),MAX(cqk30) 







  FROM cqk_file  







 --where cqk03 = 2012 and cqk031 = 3   







 GROUP BY cqk05,cqk03,cqk031      --多列分組   







 HAVING COUNT(distinct cqk09)>=3  







 ORDER BY 1 DESC,2 ASC,3 ASC

CQK05 CQK03 CQK031 MIN(CQK30) AVG(CQK30) MAX(CQK30)

-------          -------         --------     -------------      --------------       ----------------
2SF1            2012            3                   52210              52210                  52210
2SF1            2012            4                   52210              52210                  52210
2S90            2012            3                   24333             48421                   72509
2S90            2012            4                   26806             49657.5              72509
2S80            2012           3                   26345             45312                   64279
2S80            2012            4                   28848             46563.5                 64279

ROLLUP生成横向统计信息








--统计2G80部门各人员所有年月内的均资、最低薪资、最高薪资   







SELECT cqk09,AVG(cqk30),MIN(cqk30),MAX(cqk30) FROM cqk_file  







 WHERE cqk05 = '2G80'  







 GROUP  BY ROLLUP (cqk09) --rollup 均资再平均，最低再求最低，最高再求最高

CQK09 AVG(CQK30) MIN(CQK30) MAX(CQK30)

-------      ---------------      ---------------        ---------------
张三               83859.25     36156                 90674
李四         28752.36364     14905                 30300
王五         31184.58333     6579                    33494
赵六                    23942     16405                 31479
钱七                   45496     23480                  47702
王八               29241.25     29218                 29249
九九                    38760     38760                  38760
黄十               36462.75     36432                   36473
匡二                   16833     16833                  16833
刘一         56484.66667     54809                  56665
                41848.31707     6579                    90674

GROUPING SETS 按多行多列分别统计信息








--查询统计2013/08/15日各订单的接单本币金额及各币种的金额分布







SELECT oea01 as "订单号码",oea23 as "原币币别",SUM(oeb14*oea24) as "金额统计" 







  FROM oeb_file







  JOIN oea_file ON oea01 = oeb01







 WHERE oea02 >= to_date('2012/04/15','YYYY/MM/DD')







 GROUP BY grouping sets(oea01,oea23)

订单号码原币币别金额统计

-----------------     -----------    ---------------
ZZ1-12040413                       12573.1584
KK3-12040009                       19168.812
ZZ1-12040432                       5739.51
. . .                                            . . .
                                    HKD 1833838.062
                                    NTD   145010
                                    USD   5554649.988

-----------------------------------------------------------------------------------------------------------------------------------------

12、如何查询某时刻点提交的数据资料

利用在from子句中指定as of子句,在as of子句中指定时间，也可以指定SCN

前提：数据库必须采用UNDO管理模式，并且初始化了undo_retention限制undo数据保留的时间








--应用时间点返回历史查询数据  







SELECT * FROM oea_file as of timestamp to_timestamp('2012/04/16 11:01:00','YYYY/MM/DD HH24:MI:SS') 







--应用SCN序列号返回历史查询数据，需DBA的权限







SELECT * FROM oea_file as of scn scn序列号

-----------------------------------------------------------------------------------------------------------------------------------------

13、CASE及递归查询等复杂应用.....

CASE应用语法：

CASE

WHEN condition1 THEN....

WHEN condition2 THEN....

ELSE ....

END








--CASE语法应用：查询ERP用户信息，判断其部门信息







SELECT zx01,zx02,zx03,  







  (CASE   







      WHEN zx03 = '2G69'  







        THEN '资讯部'  







      WHEN zx03 = '2G88' OR zx03 = '2G89'  







        THEN '财务部'  







      WHEN zx03 IS NULL  







        THEN '无部门'  







      ELSE  







        '其他部门'  







   END  







  )department   







  FROM zx_file  







  ORDER by zx01

ZX01 ZX02 zx03 DEPARTMENT

--------   --------   -------     ----------------
acc        會ACC                  无部门
zhanna 詹妹       2G89      财务部
amy72   葉妹       2G21      其他部门
tcx         童哥       2E10      其他部门
terry      李某       2G69      资讯部

WITH 表名 as (SQL子查询语句)：可以将(SQL子查询语句复用)

START WITH ... CONNECT BY PRIOR ... 实现Oracle层次查询

START WITH：用于指定层次查询的起始根行，当然也可以指定末行开始查询

CONNECT BY：用于指定父子行关系，在其后必须使用PRIOR引用父行








--递归层次显示料号11MP28440ZZZZ001110-004101的BOM结构







SELECT DISTINCT concat(lpad(' ',3*(level-1)),bmb01)   father_ima,







                concat(lpad(' ',3*(level+1-1)),bmb03) level_ima 







  FROM bmb_file,bma_file







 WHERE bma01=bmb01 AND bma10='2' AND bmb14='0'







 START WITH bmb01 = '11MP28440ZZZZ001110-004101' 







        AND bmb01 IS NOT NULL    AND bmb14='0'







        AND (bmb05 IS NULL OR bmb05>sysdate)







        AND bmb04<=sysdate







 CONNECT BY PRIOR bmb03 =  bmb01  --表示当前料号的bmb03是下阶料号的bmb01 







 ORDER BY father_ima DESC

FATHER_IMA LEVEL_IMA

----------------------------- --------------------------

11MP28440ZZZZ001110-004101	B01288119000-002587
11MP28440ZZZZ001110-004101	D281NP10100000
11MP28440ZZZZ001110-004101	D282FEP0000001
B01288119000-002587	D1120080000000
B01288119000-002587	P50100079000000000-006447
P50100079000000000-006447	C2418001ZZZZ00114-0009
P50100079000000000-006447	C2418010ZZZZ00114-0009
C2418010ZZZZ00114-0009	D281NP10100000
C2418010ZZZZ00114-0009	D282FEP0000010
C2418010ZZZZ00114-0009	E01320072801000
C2418001ZZZZ00114-0009	D281NP10100000
C2418001ZZZZ00114-0009	D282FEP0000001
C2418001ZZZZ00114-0009	E01320072801000
E01320072801000	D1120320000000








--上述11MP28440ZZZZ001110-004101BOM结构中只查尾阶原物材料     







WITH cte_level_ima AS   --WITH .. AS SQL子查询复用







(--递归层次显示料号11MP28440ZZZZ001110-004101的BOM结构







 SELECT DISTINCT concat(lpad(' ',3*(level-1)),bmb01)   father_ima,







                 concat(lpad(' ',3*(level+1-1)),bmb03) level_ima 







   FROM bmb_file,bma_file







  WHERE bma01=bmb01 AND bma10='2' AND bmb14='0'







  START WITH bmb01 = '06000005' 







         AND bmb01 IS NOT NULL    AND bmb14='0'







         AND (bmb05 IS NULL OR bmb05>sysdate)







         AND bmb04<=sysdate







  CONNECT BY PRIOR bmb03=bmb01 --表示当前料号的bmb03是下阶料号的bmb01 







  ORDER BY father_ima DESC  







) 







SELECT '11MP28440ZZZZ001110-004101' AS "尾阶原物材料" FROM dual







  UNION 







SELECT level_ima AS "尾阶原物材料" FROM cte_level_ima







  --where ltrim(' ',cte_level_ima.level_ima) not in (select (ltrim(' ',cte_level_ima.father_ima)) from cte_level_ima)







  WHERE cte_level_ima.level_ima NOT IN (SELECT father_ima FROM cte_level_ima)







  ORDER BY 1 DESC

尾阶原物料材料

---------------------------------------

11MP28440ZZZZ001110-004101
   D282FEP0000001
   D281NP10100000
      D1120080000000
            D282FEP0000010
            D282FEP0000001
            D281NP10100000
               D1120320000000








--应用：Tiptop GP ERP axcr610(LCM存货货龄分析表),一条SQL搞定30、60、90、180、360天呆滞料表







WITH cte_cma_file AS







(SELECT cma01,cma02,cma03,cma04,cma14,cma15,cmc03,cmc04







   FROM cma_file,cmc_file







  WHERE cma01 = cmc01







    AND cma15 <> 0







    AND cma021 = cmc021 AND cma022 = cmc022







    AND cma02 = to_date('2012/05/31','YYYY/MM/DD')







    AND cma021 = '2012' AND cma022='5'







  ORDER BY cma01,cma02







),







     cte_cma_count AS







(SELECT cma01,







        SUM(cmc04*(CASE WHEN ((cma02-cmc03)>=0    AND (cma02-cmc03)<=30)  THEN 1 ELSE 0 END)) cmc04_t30,







        SUM(cmc04*(CASE WHEN ((cma02-cmc03)>=31   AND (cma02-cmc03)<=90)  THEN 1 ELSE 0 END)) cmc04_t90,







        SUM(cmc04*(CASE WHEN ((cma02-cmc03)>=91   AND (cma02-cmc03)<=180) THEN 1 ELSE 0 END)) cmc04_t180,







        SUM(cmc04*(CASE WHEN ((cma02-cmc03)>=181  AND (cma02-cmc03)<=365) THEN 1 ELSE 0 END)) cmc04_t365,







        SUM(cmc04*(CASE WHEN ((cma02-cmc03)>=366)                         THEN 1 ELSE 0 END)) cmc04_t365UP







   FROM cte_cma_file







  GROUP BY cma01







  ORDER BY cma01







),







     cte_cma_price AS







(SELECT DISTINCT cma01,cma14,cma15







   FROM cte_cma_file







  ORDER BY cma01







)







SELECT cte_cma_count.cma01 "料号",cma14 "月平均加权单价",cma15 "总数量",cma14*cma15 "总金额",







       cmc04_t30    "30天呆滞总数量",   cmc04_t30*cma14    "30天呆滞总金额",







       cmc04_t90    "90天呆滞总数量",   cmc04_t90*cma14    "90天呆滞总金额",







       cmc04_t180   "180天呆滞总数量",  cmc04_t180*cma14   "180天呆滞总金额",







       cmc04_t365   "365天呆滞总数量",  cmc04_t365*cma14   "365天呆滞总金额",







       cmc04_t365UP "365UP天呆滞总数量",cmc04_t365UP*cma14 "365UP天呆滞总金额"







  FROM cte_cma_count,cte_cma_price







 WHERE cte_cma_count.cma01 = cte_cma_price.cma01

Oracle SQL：select各类查询语句总结的更多相关文章

对于Oracle中分页排序查询语句执行效率的比较分析
转自:http://bbs.csdn.net/topics/370033478 对于Oracle中分页排序查询语句执行效率的比较分析作者:lzgame 在工作中我们经常遇到需要在Oracle中进行分 ...
SQL结构化查询语句
SQL结构化查询语句 SQL定义了查询所有关系型数据库的规则. 1.通用语法 SQL语句可以单行或者多行书写,以分号结尾可以使用空格和缩进增强可读性不区分大小写,但是关键字建议大写 3种注释注释 ...
Oracle SQL ANY和ALL语句
Oracle的嵌套子查询可以使用Some,Any和All对子查询中返回的多行结果进行处理. Some表示满足其中一个的含义,是用or串起来的比较从句. 例如:SELECT * FROM emp WHE ...
oracle SQL多表查询
SQL多表查询 1.集合理论 1.1 什么是集合具有某种特定性质的事物的总体. 集合的特性:无序性.互异性.确定性. 一个集合可以小到从一个表中取出一行中的一列. 1 ro ...
Oracle性能优化之查询语句通用原则
作者早期文章 Oracle优化索引是表的一个概念部分 , 用来提高检索数据的效率, ORACLE 使用了一个复杂的自平衡 B-tree 结构 . 通常 , 通过索引查询数据比全表扫描要快 . 当 O ...
SQL Server-简单查询语句，疑惑篇（三）
前言对于一些原理性文章园中已有大量的文章尤其是关于索引这一块,我也是花费大量时间去学习,对于了解索引原理对于后续理解查询计划和性能调优有很大的帮助,而我们只是一些内容进行概括和总结,这一节我们开始正 ...
sql的基本查询语句
--------------------------------------------基本常用查询-------------------------------------- 自己简单练习做了个表. ...
常用oracle中系统表查询语句
sqlplus / as sysdbaSQL>select status from v$instance;1.查看最大连接数show parameter processes;2.查询oracle ...
Oracle中的一些查询语句及其执行顺序
查询条件: 1)LIKE:模糊查询,需要借助两个通配符,%:表示0到多个字符:_:标识单个字符. 2)IN(list):用来取出符合列表范围中的数据. 3)NOT IN(list): 取出不符合此列表 ...

随机推荐

使用enum建立简单的状态机
Overview The enum in Java is more powerful than many other languages which can lead to surprising us ...
【转】T-SQL 教程
USE [test] GO /****** Object: StoredProcedure [dbo].[PageIndex] Script Date: 12/07/2011 10:26:36 *** ...
React Native教程 - 调用Web API
react-native官网Fetch介绍:https://facebook.github.io/react-native/docs/network.html#content react-native ...
ural 1066 uva 1555
好吧竟然因为编译器的问题不过到底有什么区别 ???? 可以推出公式Hi = (i-1)H2 +(i-1)(i-2)-(i-2)*H1 因为所有的Hi都要大于零 Hn要最小即存在Hi=0 ...
IOS pop使用代理传值
假如oneViewController页面push到OtherViewController页面,然后你想从OtherViewController页面pop到oneViewController页面的时候 ...
&amp&semi;nbsp&semi; 与空格的区别
nbsp 是 Non-Breaking SPace的缩写,即“不被折断的空格”,当两个单词使用连接时,这两个单词就不会被分隔为2行,如下面 <div id="div1" ...
（转）web&period;config详解之在文件中配置网站默认页面
在<configuration></configuration>中添加下面的配置 <system.webServer> <defaultDocu ...
AOP的简单练习
---恢复内容开始--- 1.AOP的主要作用及概念简介 AOP最大的用处在于事务处理上,业务层在项目中主要负责以下的操作: ·调用数据层进行处理: ·进行事务的处理: ·关闭数据库的连接操作: 但在 ...
20162328蔡文琛week05
学号 20162328 <程序设计与数据结构>第X周学习总结教材学习内容总结面向对象程序设计的核心是类的定义,它代表定义了状态和行为的对象. 变量的作用域依赖于变量声明的位置,作用域决 ...
ajax执行成功不进入success方法
当dataType的值为json时,传入的值和返回的值符合json格式的时候,执行成功才会进入success方法,否则进入error方法.