每日分享工作中比较常用的东西,加强记忆

1.over(partition by)开窗函数,与聚合函数的区别是聚合函数返回一行数据,该函数分组返回多组数据。

例如:

   SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm,count(*) over(partition by t.CLASS) from T2_TEMP t) ;

注:rank()跳排名 1,1,3,4。

       dense_rank连续排序 1,2,3,4。

       *row_number()有去重复的功能。排名相同的只显示一个。

       count() over(partition by ... order by ...):求分组后的总数。

   max() over(partition by ... order by ...):求分组后的最大值。
   min() over(partition by ... order by ...):求分组后的最小值。
   avg() over(partition by ... order by ...):求分组后的平均值。
   lag() over(partition by ... order by ...):取出前n行数据。  

   lead() over(partition by ... order by ...):取出后n行数据。

2.to_number()字符串强转成数字

3.sys_connect_by_path()

  比较使用行转列的函数,注意需要把表转成树的形式才可使用!

  例子:

    select MAX(substr(sys_connect_by_path(name, ','),2))  from (SELECT name,rn id,LAG(RN) OVER(ORDER BY RN) pid FROM(select temp.name,rownum rn from T2_Temp temp))  start with pid is null connect by prior id=pid

  start with 条件 connect by prior 条件,树形式。

  此例子是通过lag()把结果集转换成了树结构。很实用。

4.wm_concat()函数

    行转列select id,wm_concat(subname) from(...)group by id

5.pivot

   列转行

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]

FROM(SELECT DaysToManufacture, StandardCost     FROM Production.Product) AS SourceTable

PIVOT(AVG(StandardCost)FOR DaysToManufacture IN ([0], [1], [2], [3], [4])) AS PivotTable;


阅读更多
换一批

没有更多推荐了,返回首页