SQL查询语句总结
数据查询语言：即DML(Data Mannipulation Language)数据操纵语言，用于查询、操纵数据表资料行
本手册所有示例引用表均以TIPTOP GP ERP数据库表为基础演示，
TIPTOP 数据字典下载地址：http://pan.baidu.com/s/1jG1PGF0
1、SELECT 查询所有列资料或特定列资料
--查询订单产品,产品品名资料
SELECT oeb04,oeb06 FROM oeb_file;-------------- ------------
J00020853 F3D111-15
J00020886 F2A036-25
J00020901 F2A046b10-GLD
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')--------- ---------- ------ ----------
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 = gem01ZX01 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 joinZX01 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%')-------- --------- ------
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部门资讯,所以子查询返回的条件为trueZX01 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'-------- ----------- ------
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'-------- ----------- ------
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 ASCCQK05 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 均资再平均，最低再求最低，最高再求最高------- --------------- --------------- ---------------
张三 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 zx01ZX01 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 DESCFATHER_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
ORDER BY cte_cma_count.cma01http://blog.csdn.net/yihuiworld
