oracle标量子查询是什么,Oracle -- 标量子查询、表子查询、开窗函数

1.子查询sql

子查询：SQL语句容许将一个查询语句作为一个结果集供其余SQL语句使用，就像使用普通表同样，被当作结果集的查询语句被称为子查询。框架

子查询分2种：1)只返回一个单值的子查询，可看作是一个拥有返回值的函数。函数

2)返回一列值的子查询，这时子查询能够看做是一个在内存中临时存在的数据表。spa

子查询分标量子查询、表子查询。code

标量子查询：子查询的返回值必须只有一行记录，并且只能有一个列。能够用在select语句的列表中、表达式中、where语句中等。排序

表子查询：列值子查询能够返回一个多行多列的结果集。表子查询能够看做一个临时表，表子查询能够用在select语句的from子句、insert语句、链接、in子句等。内存

2.group by与开窗函数ci

要求将每张销售单中销售量最大的明细记录标记出来。it

select x1,

x2,

x3,

x4,

case when x4=max(x4)

then '单内最大值'

else ''

end,

x5

from y

group by x1;

上面的语句会报错，由于x二、x三、x5没有包含在聚合函数的group by子句中。io

select x1,

x2,

x3,

x4,

case when x4=max(x4)

then '单内最大值'

else ''

end,

x5

from y

group by x1,x2,x3,x4,x5;

上面的SQL语句能执行经过，可是执行结果是错误的，由于将select列表中的全部列都放到了group by子句中会破坏原有的分组。下面讲解使用聚合函数而没必要将select列表中的全部列都放到group by子句中的方法。

使用窗口函数将无需使用group by子句，并且窗口函数中的聚合计算不会影响其余列。因此对于支持窗口函数的DBMS可使用以下的SQL语句：

select x1,

x2,

x3,

x4,

case when x4=max(x4) over(partition by x1)

then '单内最大值'

else ''

end,

x5

from y;

MYSQL、MSSQLServer2000等不支持窗口函数的DBMS，可使用相关子查询达到相同的效果。如：

select x1,

x2,

x3,

x4,

case when x4=(select max(x4) from y t where t.x1 = t1.x1)

then '单内最大值'

else ''

end,

x5

from y t1;

上面使用相关子查询来计算每一个销售单中的销售量的最大值，其他部分与使用窗口函数是同样的。需注意的是相关子查询中的where子句中将t.x1 = t1.x1进行相等性过滤。这样能够作到窗口函数中“partition by x1”同样的分区计算最大值的效果，所以这个where语句不能缺失。

当须要使用聚合计算，又不但愿因为引入聚合函数而须要添加额外的group by子句的话，可使用：支持窗口函数的DBMS使用窗口函数，不支持窗口函数的DBMS使用子查询。

3.开窗函数：over()

over()关键字表示把函数当成开窗函数而不是聚合函数。SQL标准容许将全部的聚合函数用做开窗函数，使用over关键字对这2种方法进行区分。

4.partition by子句

开窗函数的over关键字后括号中的可使用partition by子句来定义行的分区来进行聚合计算。与group by子句不一样，partition by子句建立的分区是独立于结果集的，建立的分区只是供进行聚合计算的，并且不一样的开窗函数所建立的分区也互不影响。

5.开窗函数中的order by子句

order by子句的语法：ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2

range表示按照值的范围进行范围的定义，而rows表示按照行的范围进行范围的定义。边界规则的可取值以下：

可取值

说明

示例

current row

当前行

N preceding

前N行

2 preceding

unbounded preceding

一直到第一条记录

N following

后N行

2 following

unbounded following

一直到最后一条记录

“RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2”部分用来定位聚合计算范围，这个子句又称为定位框架。

其中rows是按照行数进行范围定位的，而range则是按照值范围进行定位的。这2种不一样的定位方式主要用来处理并列排序的状况。如10,20,20,20若是按照rows进行范围定位，则计算从第一条到当前行的累积和，即10,30,50,70。若是按照range进行范围定位，则仍然计算从第一条到当前行的累积和，即10,70,70,70。

若使用的是range between unbounded preceding and current row，为简化使用，可省略定位框架的声明部分。即sum(x) over(order by x1 range between unbounded preceding and current row)等价于sum(x) over(order by x1)。