彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > T-SQL:开窗函数(十七卡塔 尔(阿拉伯语:قطر‎

T-SQL:开窗函数(十七卡塔 尔(阿拉伯语:قطر‎

发布时间:2019-11-15 06:53编辑:工作委员会浏览(161)

    1.基本概念

    1.创建测试表score

    开窗函数分为两个部分分别是

    create table score(
    class_no varchar2(10),      --班级
    student_name varchar2(20),  --姓名
    score number                --分数
    );
    

    1.聚合,排名,偏移,分布函数 。

    2.初始化数据

    2.开窗分区,排序,框架。

    insert into score(class_no,student_name,score) values('n001','park',99);
    insert into score(class_no,student_name,score) values('n001','ning',99);
    insert into score(class_no,student_name,score) values('n001','tom',79);
    insert into score(class_no,student_name,score) values('n001','cat',87);
    insert into score(class_no,student_name,score) values('n001','sandy',95);
    insert into score(class_no,student_name,score) values('n002','cake',85);
    insert into score(class_no,student_name,score) values('n002','mavom',69);
    insert into score(class_no,student_name,score) values('n002','tony',90);
    insert into score(class_no,student_name,score) values('n002','lisa',99);
    insert into score(class_no,student_name,score) values('n002','linda',67);
    insert into score(class_no,student_name,score) values('n003','versy',84);
    insert into score(class_no,student_name,score) values('n003','peter',97);
    insert into score(class_no,student_name,score) values('n003','train',83);
    insert into score(class_no,student_name,score) values('n003','rain',80);
    

    彩世界时时app,彩世界开奖app苹果下载,下面举个例子

    3.将不同班级学生按分数降序排列

    SELECT empid, ordermonth, val,
      SUM(val) OVER(PARTITION BY empid
                    ORDER BY ordermonth
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runval
    FROM Sales.EmpOrders;
    
    select *
      from (select class_no,
                   student_name,
                   score,
                   rank() over(partition by class_no order by score desc)
              from score) t;
    

    sum(val)  就是集合函数

    4.执行结果

    over() 就是开窗     PARTITION BY empid  就是开窗分区(分组)   ORDER BY ordermonth 开窗排序  

    彩世界开奖app苹果下载 1

     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  开窗架构

    5.其他分析函数

    2.排名开窗函数

    row_number() over(partition by ... order by ...)
    rank() over(partition by ... order by ...)
    dense_rank() over(partition by ... order by ...)
    count() over(partition by ... order by ...)
    max() over(partition by ... order by ...)
    min() over(partition by ... order by ...)
    sum() over(partition by ... order by ...)
    avg() over(partition by ... order by ...)
    first_value() over(partition by ... order by ...)
    last_value() over(partition by ... order by ...)
    lag() over(partition by ... order by ...)
    lead() over(partition by ... order by ...)
    

     SQL SERVER 支持4个排名函数 ROW_NUMBER,RANK,DENSE_RANK ,NTLE   来看看它们分别的作用

     

    SELECT orderid, custid, val,
    ROW_NUMBER() OVER(ORDER BY val) AS rownum,
    RANK() OVER(ORDER BY val) AS rank,
    DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
    NTILE(10) OVER(ORDER BY val) AS ntile
    FROM Sales.OrderValues
    ORDER BY val;
    

    彩世界开奖app苹果下载 2

    可以看到 它们不同排序规则

    ROW_NUMBER() 对排序字段行号进行排序  

    RANK() 对数值进行排序 对相同数值有行号占用

    本文由彩世界平台发布于工作委员会,转载请注明出处:T-SQL:开窗函数(十七卡塔 尔(阿拉伯语:قطر‎

    关键词:

上一篇:没有了

下一篇:没有了