甲骨论2012版oracle教程相克军老师sql高级查询

使用集合操作符集合操作符可以将两个或者多个查询返回的行组合起来。1、unionall：返回各个查询检索出的所有行，包括重复的行2、union：返回各个查询检索出的所有行，不包括重复行3、intersect：返回两个查询检索出的共有行4、minus：返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的记录UNION和UNIONALL的区别：1.在查询中会遇到UNIONALL,它的用法和union一样,只不过union含有distinct的功能,它会把两张表重复的记录去掉,而unionall不会,所以从效率上,unionall会高一点.2.unionall是按原先顺序排列的，union把两张表中的重复数据去掉后还进行了重新排序MINUS：指令是运用在两个SQL语句上。它先找出第一个SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔资料就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃。注意：所有查询所返回的列数以及列的类型必须匹配，列名可以不同。使用unionall操作符返回查询所检索出的所有行，包括重复行。多了一个distinct的操作。返回共有行。从第一个查询中减去共有行。组合使用集合操作符（注意括号的作用）改变了执行顺序，结果就完全不一样。TRANSLATE()函数下面的例子可以很好的说明这个函数的意义将这个字符串的A转换成为E、B转换成为F，以此类推。TRANSLATE(x,from_string,to_string)数字也可以转换。decode(x,search_value,result,default_value)对x进行search，如果和search_value相同，那么就返回结果result，否则就返回default_value。decode()允许在SQL中执行if-else-then类型的逻辑处理，而不需要使用PL/SQL。经典用法一经典用法二是用CASE表达式CASE表达式可以在SQL中实现if-else-then，而不使用PL/SQL，CASE和decode()类似，但是我们应该尽量使用CASE，因为他是ANSI标准。CASEsearch_expression WHENexpression1THENresult1 WHENexpression2THENresult2 …….. WHENexpressionNTHENresultN ELSEdefault_resultEND每一行数据都会比较一次。经典用法一经典用法二经典用法三层次化查询在有些表数据中，数据是层次化的，因此我们在显示这些数据的时候，希望以层次化的显示出来。层次化结构的数据，例如人力资源数据。这里面的manager_id列自引用了表的employee_id列。JamesSmithCEORonJohnsonSalesmanagerFredHobbsSalesPersonSusanJonesSupportManagerRobGreenSalesPersonJaneBrownSupportPersonJohnGreySupportManagerJeanBlueSupportpersonHenryHeysonSupportPersonKevinBlackOpsManagerKeithLongOpsPersonFrankHowardOpspersonDoreenPennOpsPerson根节点：位于树顶端的节点父节点：父节点的下面有一个或者多个节点子节点：子节点之上具有一个父节点叶节点：没有子节点的节点要执行层次化查询，需要使用startwith加上connectbypriorSELECT[level],column,expression,…FROMtable[WHEREwhere_clause][[startwithstart_condition][connectbypriorprior_condition]]level：是一个伪列，代表位于树的第几层，对于根来说，level返回1，根节点的子节点返回2，依此类推。start_condition定义了层次化查询的起点，当编写层次化查询时必须指定startwith子句prior_condition定义了父行和子行之间的关系，当编写层次化查询时必须定义connectbyprior子句。employee_id=manager_id表示父节点的employee_id和子节点的manager_id之间存在关系1、首先拿出employee_id=1的记录，放在根节点2、按照employee_id的顺序，依次拿出所有的记录，这些记录摆放的位置取决于他们的manager_id，根据manager_id=employee_id，依次将下面的各个记录放在相应的位置。从上到下，从左到右的顺序进行存放。查看树有几层。格式化输出整个的树结构，采用的是从上到下，从左到右。JamesSmithCEORonJohnsonSalesmanagerFredHobbsSalesPersonSusanJonesSupportManagerRobGreenSalesPersonJaneBrownSupportPersonJohnGreySupportManagerJeanBlueSupportpersonHenryHeysonSupportPersonKevinBlackOpsManagerKeithLongOpsPersonFrankHowardOpspersonDoreenPennOpsPerson从非根节点开始遍历，也要遵循这个特点1、startwith的起点表示的就是根节点，如果以SusanJones作为起点，那么这就是根节点2、只生成一棵树，对于左右两边的树都不会被遍历使用employee_id可以防止重复。在startwith子句中使用子查询从下向上遍历树从某个子节点开始，自下而上进行遍历。实现的方法是交换父节点与子节点在connectbyprior子句中的顺序。从层次查询中删除节点和分支删除了一个节点，但是子节点还存在。删除了一个分支。JamesSmithCEORonJohnsonSalesmanagerFredHobbsSalesPersonSusanJonesSupportManagerRobGreenSalesPersonJaneBrownSupportPersonJohnGreySupportManagerJeanBlueSupportpersonHenryHeysonSupportPersonKevinBlackOpsManagerKeithLongOpsPersonFrankHowardOpspersonDoreenPennOpsPerson在层次化查询中加入其他条件使用扩展的groupby函数1、rollup2、cube下面是示例表使用rollup子句这个子句扩展了groupby子句，为每一个分组返回一条小计记录，并为全部分组返回总计。groupby的经典用法。使用rollup最后一行包含所有分组的工资总计。向rollup传递多列1、首先是根据两个字段进行分组和聚合。2、然后根据第一个字段再次进行分组和聚合3、最后对所有的分组进行聚合修改传递给rollup的列的位置ROLLUP和其他聚合函数一起使用使用cube子句这个子句对groupby进行了扩展，返回cube中所有列组合的小计信息，同时在最后显示总计信息。Oracle9i放在末尾，oracle10g放在开始处。使用grouping函数这个函数可以接受一列，返回0或者1，如果列值为空，返回1，如果列值非空，则返回0.这个函数只能和rollup、cube一起使用。使用decode()转换grouping()的返回值使用decode和grouping转换多个列的值使用cube与grouping结合使用使用groupingsets子句使用groupingsets子句可以只返回小计记录使用grouping_id()函数可以使用grouping_id函数借助having子句对记录进行过滤，将不包含小计或者总计的记录除去。我们来看一个例子grouping_id(division_id,job_id)division_id job_id 位向量 grouping_id()返回值非空

非空 00 0非空

空 01 1空

非空 10 2空

空 11 3只返回小计和总计。在GROUPBY子句中多次使用一个列一个列在groupby中出现多次，并不会影响分组的数量。有记录重复。group_id()函数没有接受任何的列。使用having去除多余的行。使用分析函数数据库中有很多内置的分析函数，能够执行复杂的计算。分析函数可以分为以下几类：1、评级函数2、反百分点函数3、窗口函数4、报表函数5、延迟与领先函数6、首函数与末函数7、线性回归函数8、假象评级及分布函数示例表使用评级函数1、使用rank()和dense_rank()函数这两个函数可以计算数据项在分组中的排名。两者的区别可以看一个例子。当出现两个第一的时候，rank()保留两个第一，下一个就是第三，而dense_rank()的下一个则是第二。出现了空值，而空值的排名居然在第一位。因为我们使用的是递减排序，如果是ASC，空值排在最后面。使用NULLSFIRST和NULLSLAST子句控制空值的排名。分析函数结合partitionby子句在sum(amount)列上进行排名。但是只是同一个月份进行比较排名。也就是只在month相同的行上进行比较。四个数字进行比较，得出1-4分析函数与ROLLUP、CUBE、GROUPINGSETS操作符结合使用使用rollup，多了一个行，这个行也参与了排名。在sum(amount)列上多出了很多的行。这些行也参与了排名。销量小计进行了排名。使用cume_dist()和percent_rank()函数这个行的第几行数/行的总数。1/51+1/51+1+1/51+1+1+1/5 1+1+1+1+1/5累积1-1/5-12-1/5-13-1/5-14-1/5-15-1/5-1行序号/n-1sql>selectid,value,percent_rank()over(orderbyid)asprfromtest017;IDVALUEPR------------------------------112303345.254456.55567.7565671如果有重复元素呢？sql>selectid,value,percent_rank()over(orderbyid)asprfromtest017;IDVALUEPR------------------------------11230123403345.44456.65567.865671其中开头两行id重复。sql>selectid,value,percent_rank()over(orderbyid)asprfromtest017;IDVALUEPR------------------------------112302234.23345.43456.45567.865671sql>selectid,value,percent_rank()over(orderbyid)asprfromtest017;IDVALUEPR------------------------------312303234033450345603567065671sql>selectid,value,percent_rank()over(orderbyid)asprfromtest017;IDVALUEPR------------------------------312303234033450345605567.865671应该是(5-1)/5=0.8(6-1)/5=1;sql>selectid,value,cume_dist()over(orderbyid)asprfromtest017;IDVALUEPR------------------------------3123.53234.53345.55456.8333333335567.83333333365671应该是行数/总行数。如果有重复的，如上：则前3行是3/6接着2行是5/6。被除数以重复行的最后一行的行数为准。使用ntitle函数ntile(buckets)功能描述：将一个组分为“buckets"的散列表示，例如，如果buckets=4，组中有20行，则给前5行分配1，给下5行分配2等等。如果组的基数不能由表达式值平均分开，则对这些行进行分配时，最低的percentile是那些拥有额外行的percentile。例如，若表达式=4，行数=21，则percentile=1的有6行，percentile=2的有5行等等。sql>selectid,value,ntile(4)over(orderbyvalue)asquartilefromtest017;ID VALUE QUARTILE---------- ---------- ----------1123133451445625567365674使用row_number()row_number()over(partitionbycol1orderbycol2)表示根据col1分组，在分组内部根据col2排序而这个值就表示每组内部排序后的顺序编号使用窗口函数窗口不是固定的，随着rownum的增加，窗口在改变，窗口的大小不变，但是窗口的位置在改变。因此常说的窗口通常指的就是移动窗口。计算累计和计算销售总和按照月份对查询读取的记录进行排序窗口大小1、窗口的起点：查询所读取的所有行2、窗口的终点：当前行（默认，可以不指定）默认的终点就是当前行。计算移动平均值计算本月与前三个月之间的销量的移动平均值窗口起点为当前记录的前面第三条记录。窗口终点为当前记录（默认值）计算中心平均值计算当前月份前、后各一个月内的销量平均值。用first_value()和last_value()获取第一条记录和最后一条记录。将当前月份的销量除以前一个月份的销量，并将当前月份的销量除以下一个月份的销量。使用报表函数1、总计报表下面的查询报告2003年前三个月的： 1、每个月的销量总和（total_month_amount） 2、所有产品类型销量的总和（total_product_type）所有产品在一月份的销量总和每一种产品在前三个月的销量总和，一共有5种产品，其中第五种产品没有销量。使用ratio_to_report()函数这个函数可以用来计算某个值在一组值的总和中所占的比率。下面这个产品报告2003年前三个月的：1、每种类型每个月的销量总计（prd_type_amount）。2、该产品类型的销量占整月销量的比率（prd_type_ratio）使用lag()和lead()函数lag()和lead()函数可获得位于距当前记录指定距离处的那条记录中的数据。下面的查询用lag()和lead()获得前一个月和后一个月的销量。使用first和last函数first函数和last函数可获得一个排序分组中的第一个值和最后一个值。下面的这个查询用first和last获得2003年中销量最高和最低的月份。使用假想评级与分布函数假想评级与分布函数可以计算一条新纪录在表中的排名和百分比，而不用将其插入表中。下面的查询使用rank()和percent_rank()获得2003年每一种产品类型销量的排名和百分比排名。这个查询计算销量$500,000的假象排名和百分比排名。使用model子句Oracle10g中新增的model子句可以用来进行行间计算。Model子句允许像访问数组中的元素那样访问记录中的某个列。….示例model子句下面的查询获取2003年内由员工#21完成的产品类型为#1和#2的销量，并根据2003年的销售数计算出2004年1月、2月、3月的销量预测值。2004年的1-3月份2004年1-3月份指定结果是根据prd_type_id分区的。定义数组的维数是month和year，必须提供[month,year]才能访问某一个列。用来指定包含数量的数组中的任何一个单元，数组名为sales_amount，为了访问sales_amount数组中表示2003年1月的那个单元，可以使用sales_amount[1,2003