over partition by与group by是都是分组统计的函数。
区别
1. over partition by 其中partition by 只是over一个子句参数,作用就是分组。over 子句可以与聚合函数结合使用(max、min、sum、avg、count等).下面我们看一个例子
-- 创建表并插入数据
CREATE TABLE Employee
(
ID int identity(1,1),
EmpName varchar(20),
EmpSalary varchar(10),
EmpDepartment varchar(20)
);
INSERT INTO Employee
SELECT '张三','5000','开发部' UNION ALL
SELECT '李四','2000','销售部' UNION ALL
SELECT '王麻子','2500','销售部' UNION ALL
SELECT '张三表叔','8000','开发部' UNION ALL
SELECT '李四表叔','5000','开发部' UNION ALL
SELECT '王麻子表叔','5000','销售部'
-- 现在使用来对 部门进行分组,使用over partition by分组,按照工资排序(使用ROW_NUMBER()函数来生成一列)
SELECT * ,ROW_NUMBER() OVER(PARTITION BY EmpDepartment ORDER BY EmpSalary) EmpNumber FROM Employee
-- 执行结果如下:
ID EmpName EmpSalary EmpDepartment EmpNumber
--------- -------------------- ---------- -------------------- --------------------
1 张三 5000 开发部 1
5 李四表叔 5000 开发部 2
4 张三表叔 8000 开发部 3
6 王麻子表叔 5000 销售部 1
2 李四 2000 销售部 1
3 王麻子 2500 销售部 2
2. 假如现在 使用 over partition by 分组,并取得最高工资的员工信息,我们看看会有什么问题
SELECT * ,MAX(EmpSalary) OVER(PARTITION BY EmpDepartment) MaxSalary FROM Employee
-- 执行结果
ID EmpName EmpSalary EmpDepartment MaxSalary
----------- -------------------- ---------- -------------------- ----------
1 张三 5000 开发部 8000
4 张三表叔 8000 开发部 8000
5 李四表叔 5000 开发部 8000
6 王麻子表叔 5000 销售部 5000
2 李四 2000 销售部 5000
3 王麻子 2500 销售部 5000
(6 行受影响)
查看执行结果不难发现,最大工资是获取到了,可是不需要的列也显示出来了。
使用Group by来分组就没有(相信可能使用over partition by 也可以实现如group by 这样的分组)
SELECT EmpDepartment,MAX(EmpSalary) FROM Employee
GROUP BY EmpDepartment
-- 执行结果
EmpDepartment
-------------------- ----------
开发部 8000
销售部 5000
(2 行受影响)
group by是对检索结果的保留行进行单纯分组,一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。 (官方参考文档:http://technet.microsoft.com/zh-cn/library/ms177673.aspx)
partition by虽然也具有分组功能,但同时也具有其他的高级功能。(可以参考官方文档:http://technet.microsoft.com/zh-cn/library/ms189461.aspx)
注:如上如有错误地方,欢迎大家指出,互相学习。