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)。