• Oracle开窗函数 over()(转)


    copy文链接:http://blog.csdn.net/yjjm1990/article/details/7524167#,http://www.2cto.com/database/201402/281473.html

    格式:

      可以开窗的函数(..) over(..)

      over中防止分组的条件和分组的排序,不过分组使用的不再是GROUP BY而是PARTITION BY,表示开窗

    -- 建表
    CREATE table tb_sc(
    uName varchar2(10),
    uCourse varchar2(10),
    Uscore varchar2(10)
    );
    -- 插入数据
    INSERT INTO tb_sc VALUES('张三','语文','80');
    INSERT INTO tb_sc VALUES('张三','数学','95');
    INSERT INTO tb_sc VALUES('李四','语文','90');
    INSERT INTO tb_sc VALUES('李四','数学','70');
    INSERT INTO tb_sc VALUES('王五','语文','90');
    INSERT INTO tb_sc VALUES('王五','数学','90');
    -- 查询所有
    SELECT * FROM tb_sc;
    -- 查询每名学生的平均分(展示姓名、平均分)
    Select uName,AVG(uScore) 
    FROM tb_sc
    GROUP BY uName;
    -- 查询每名同学的平均分并降序排列(展示姓名、平均分)
    SELECT uName,AVG(uScore) 
    FROM tb_sc
    GROUP BY uName
    ORDER BY uName DESC;
    -- 查询平均分数高于85分的学生(展示姓名、平均分)
    SELECT uName,AVG(uScore)
    FROM tb_sc
    GROUP BY uName
    HAVING AVG(uScore)>85;
    -- 查询不为张三且平均分高于85的学生(展示姓名、平均分)
    SELECT uName,AVG(uScore)
    FROM tb_sc
    GROUP BY uName
    HAVING uName != '张三' AND AVG(uScore) >85;
    -- 查询所有学生的信息并将每个学生的各科成绩降序
    SELECT t.*,ROW_NUMBER() OVER(PARTITION BY t.uName ORDER BY t.uScore DESC) RM 
    FROM tb_sc t;
    -- 查询每个学生考得最好的科目并展示该科目的成绩
    SELECT * 
    FROM(
         SELECT  t.*,row_number() OVER(PARTITION BY t.uName ORDER BY t.uScore DESC) rm
         FROM tb_sc t )
    WHERE rm=1;
    
    -- 注:row_number() over(oartition by 分组字段 order by 排序字段)常用于查询所有分组并将各个窗体进行排序
    
    -- 在开窗函数出现之前存在着很多用SQL语句很难解决的问题,很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题,在2003年ISO、SQL标准加入了开窗看书,
    -- 开窗看书的使用使得这些经典的难题可以被轻松的解决。目前在MSSQLSERVER、Oracle、DB2等主流数据库中都提供了对开窗函数的支持,遗憾的是MYSQL暂未支持
    
    -- 建表
    CREATE TABLE T_Person(
    FName VARCHAR2(20),
    FCity VARCHAR2(20),
    FAge INT,
    FSalary INT
    );
    
    -- 插入原始数据
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Tom','BeiJing',20,3000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Tim','ChengDu',21,4000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Jim','BeiJing',22,3500);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Lily','London',21,2000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('John','NewYork',22,1000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('YaoMing','BeiJing',20,3000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Swing','London',22,2000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Guo','NewYork',20,2800);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('YuQian','BeiJing',24,8000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Ketty','London',25,8500);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Kitty','ChengDu',25,3000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Merry','BeiJing',23,3500);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Smith','ChengDu',30,3000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)  
    VALUES('Bill','BeiJing',25,2000);   
    INSERT INTO T_Person(FName,FCity,FAge,FSalary)   
    VALUES('Jerry','NewYork',24,3300);  
    -- 查看表中的内容
    SELECT * 
    FROM T_Person; 
    
    -- 开窗函数简介
    -- 与聚合函数一样,开窗函数也是对行集组进行稽核计算,但是它不像普通聚合函数那样,每组只返回一个值,开窗函数可以每组返回多个值,
    -- 因为在开窗函数所执行聚合计算的行集组是窗口。在ISO、SQL规定了这样的函数为开窗函数,在Oracle中则被称为分析函数,在DB2中则被称
    -- 为OLAP函数
    
    --计算人员的总数
    SELECT COUNT(*) FROM T_Person;
    -- 查询每个工资小于5000员的员工信息(城市以及年龄),并且在每行中都显示所有工资小于5000元的员工的个数
    SELECT FCity,FAge,COUNT(*)
    FROM T_Person
    WHERE FSalary <5000
    GROUP BY FCity,FAge;
    -- 上面得到的count的值有问题,因为count统计的是分组中的总数,而分组的条件其实被改变了
    SELECT FCity,FAge,(SELECT COUNT(*) FROM T_Person WHERE FSalary <5000) 
    FROM T_Person
    WHERE FSalary <5000;
    -- 上面可以解决问题,有点恶心就是,我们使用开窗函数
    SELECT FCity,FAge,COUNT(*) OVER()
    FROM T_Person
    WHERE FSalary <5000;
    -- 可以看到与聚合函数不同的是,开窗函数在聚合函数后增加了一个OEVR关键字。
    -- 开窗函数的调用格式为: 聚合函数(...) OVER(选项)
    -- OVER关键字把函数当成开窗函数而不是聚合函数。SQL标准允许将所有聚合函数用做开窗函数,使用OVER关键字来区分这两种用法
    -- OVER中的选项使用PARTITION BY 替代GROUP BY,也可以含有ORDER BY子句.也就是说开窗函数中是分组的条件和依据,不过GROUP BY 使用PARTITION BY替代。
    SELECT FCity,FAge,COUNT(*) OVER(PARTITION BY FCity)
    FROM T_Person
    WHERE FSalary <5000;
    -- 上面我最后输出的就是城市的数量了。
  • 相关阅读:
    (step4.3.8)hdu 2181(哈密顿绕行世界问题——DFS)
    两款商业拓扑发现软件siteview和ElementSentry的比较
    20岁到30岁,我拿十年做什么
    新浪微博私信,即时聊天接口研究
    “adb server is out of date. killing.... ADB server didn't ACK * failed to start daemon * ”
    【Java&Android开源库代码剖析】のandroid-smart-image-view
    dns智能解析对网站排名的影响
    SQL Server 权限管理
    objective-c 中数据类型之四 字典(NSDictionary)
    IOS总结_实现UIButton的图文混排(二)
  • 原文地址:https://www.cnblogs.com/aigeileshei/p/6231341.html
Copyright © 2020-2023  润新知