SQL 函数 coalesce()、row_number()的用法

 

coalesce()函数

①用途：

将空值替换成其他值

返回第一个非空值

②表达式：

COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

③实例：

 

 

 

ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号。

ROW_NUMBER()

说明：返回结果集分区内行的序列号，每个分区的第一行从1开始。
语法：ROW_NUMBER () OVER  ([ <partition_by_clause> ] <order_by_clause>) 。
备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
参数：<partition_by_clause> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
      <order_by_clause>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。
返回类型：bigint 。

注意：mysql 目前使用的版本不支持该函数，提示该版本不支持，不知道后续的高版本支持不，pg 支持，所以下面实例 使用的是 pg 

 

应用实例

/*以FoodPrice列排序并显示排序后的行号*/
SELECT ROW_NUMBER() OVER(ORDER BY FoodPrice DESC) AS RowId,* FROM dbo.Food

 

/*以FoodType分组，然后以FoodPrice排序并显示所在分组的相应行号*/
SELECT ROW_NUMBER() OVER(PARTITION BY FoodType ORDER BY FoodPrice DESC) AS RowId,* FROM dbo.Food

 

RANK()

/*以FoodType分组，然后以FoodPrice排序并显示所在分组的相应行号，如果排序字段字相等则显示相同的行号，
并跳过所在的真实行号*/
SELECT RANK() OVER(PARTITION BY FoodType ORDER BY FoodPrice) AS RowID,* FROM dbo.Food

 

DENSE_RANK()

/*以FoodType分组，然后以FoodPrice排序并显示所在分组的相应行号，如果排序字段字相等则显示相同的行号，
忽略相同行号，依次递增*/
SELECT DENSE_RANK() OVER(PARTITION BY FoodType ORDER BY FoodPrice) AS RowID,* FROM dbo.Food

 

如图实际使用

-- 按状态分组，然后按单价降序展示
select  row_number() over(partition by t1.order_status order by t1.goods_price desc) as rank,t1.* from orderinfo t1

 

-- 按状态分组，然后按单价升序展示
select  row_number() over(partition by t1.order_status order by t1.goods_price asc )as rank,t1.* from orderinfo t1

 

 

-- 按状态分组，然后取出该分组中价钱最的数据

select * from (
select  row_number() over(partition by t1.order_status order by t1.goods_price desc ) as rank,t1.* from orderinfo t1) ta 
where ta.rank=1;