网站推广设计制作,百度旅游官网,招聘广告模板,wordpress主题复杂最近要完成一个项目#xff0c;有一个查询可难住了笔者#xff0c;无论是子查询还是分组#xff0c;都没弄出来#xff0c;还是基础知识不行啊。不过呢#xff0c;可以查资料#xff0c;最后用一个窗口函数解决了问题。由于开始的数据库是Access#xff0c;后来笔者导成…最近要完成一个项目有一个查询可难住了笔者无论是子查询还是分组都没弄出来还是基础知识不行啊。不过呢可以查资料最后用一个窗口函数解决了问题。由于开始的数据库是Access后来笔者导成SQL Server的一下子明白了很多。 数据库类似是这个样子滴 Employee表的字段 empId,englishName,depId Department表的字段 depId,deptName 需求是查找出Department表的所有字段但是在前面显示出该部门的人数。 就是这样 peopleCount depId deptName 25 1 人力资源部 42 2 市场营销部 一、分组的失败 首先说说分组的概念。根据关系数据库理论分组的概念是G·e 其中G是聚集·是二目运算e是G的一个成员SELECT和GROUP BY的关系如下 一当使用聚集函数例如count对于SELECT 列表中的项如果没有把它当做聚集函数的参数使用必须是分组的一部分例如有一个SQL语句 SELECT depId ,count(*) as peopleCountFROM Employee 那就必须在GROUP BY中出现deptId SELECT depId ,count(*) as peopleCountFROM Employee GROUP BY depId 但是窗口函数是例外的不必也不能出现在Group BY子句中。 而对于可能更改分组或者聚集函数返回值例如新的列则一定要包含在GROUP BY子句之中。否则就会报错。 二、窗口函数 知道了分组的基本概念之后理解窗口函数就容易了与聚集函数一样窗口函数也是针对元组就是行进行聚集但是不像聚集函数那样只返回一个值也就是聚集所有行然后计算窗口函数可以为每个分组返回多个值。执行聚集的元组行组是窗口。 例如第一个代码select count(*) as cnt from Employee 这很容易只返回一行但是往往需要从不表示聚集或者其他聚集的行中访问这种聚集数据窗口函数就解决了这个问题。例如下面的SQL语句表示用窗口函数从细节行访问聚集数据就是员工总数 SELECT EnglishName ,deptId ,count(*) over() as peopleCountFROM EmployeeORDER BY 2 OVER关键字表明把Count当成窗口函数对于查询返回的每一行它返回了表中所有行的计数括号表示还可以接收一些条件来限定行数即多一层聚集。 三、partition的使用 partition就可以成为那个括号中的条件它能够定义行的分区或者分组以完成聚集。空的括号表示分区是整个结果集。partition by是一个移动的GROUP BY例如 SELECT EnglishName ,depId count(*) over(partition by deptId) as peopleCountFROM EmployeeORDER BY 2 通过partition by depId为每个部门执行count同一个部门的每个count值相同。所以会返回很多相同的行这时可以通过内联视图的方式进行解决 SELECT DISTINCT EnglishName ,depId ,peopleCountFROM (SELECT EnglishName ,depId ,count(*) over(partition by depId) as peopleCount FROM Employee ORDER BY 2 ) x 如果要在Access中使用由于Access不支持窗口函数只能使用标量子查询代码如下 CodeSELECT DISTINCT EnglishName ,depId ,peopleCountFROM (SELECT e.EnglishName ,e.depId ,(select count(*) from emp d where e.depIdd.depId) as peopleCount FROM Employee e,Department d ORDER BY 2 ) x 需要指出的是窗口函数经过了数据库专门的优化所以性能较为优异比标量子查询要好所以应当尽量使用。转载于:https://www.cnblogs.com/tyrael007/archive/2009/04/21/1440831.html
