over partition by与group by是都是分组统计的函数。
区别
1. over partition by 其中partition by 只是over一个子句参数,作用就是分组。over 子句可以与聚合函数结合使用(max、min、sum、avg、count等).下面我们看一个例子
- <strong>-- 创建表并插入数据
- 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
- </strong>

-- 创建表并插入数据
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 分组,并取得最高工资的员工信息,我们看看会有什么问题
- <p>SELECT * ,MAX(EmpSalary) OVER(PARTITION BY EmpDepartment) MaxSalary FROM Employee</p><p>-- 执行结果
- ID EmpName EmpSalary EmpDepartment MaxSalary
- 1 张三 5000 开发部 8000
- 4 张三表叔 8000 开发部 8000
- 5 李四表叔 5000 开发部 8000
- 6 王麻子表叔 5000 销售部 5000
- 2 李四 2000 销售部 5000
- 3 王麻子 2500 销售部 5000</p><p>(6 行受影响)</p>

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 行受影响)
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)
注:如上如有错误地方,欢迎大家指出,互相学习。