【分享】，分享几个常用的分组函数，配简单事例。

[Aaron_Chan]

上次有幸和小Lee子同学，讨论到一个分组求最大值的问题，然后就没管了，今天突然有个同学问到这个问题，那么我就给他做了个简单的介绍，下面也在这里分享一下,有错误的地方，请大家改正，比较粗糙，我都只是介绍：

---------------------------------------------------------------------------------------------------------

create table Test
(
	部门 char(6),
	姓名 varchar(6),
	薪资 money
)
go
insert into Test values('市场部','张三',6000)
insert into Test values('市场部','李四',6000)
insert into Test values('市场部','王五',5000)
insert into Test values('工程部','赵柳',3400)
insert into Test values('工程部','立白',24000)
insert into Test values('工程部','雕牌',10000)
insert into Test values('策划部','海丝',2000)
insert into Test values('策划部','李波',12000)
go

--题目：找出各部门薪资最高的人

--1.常规做法：相关子查询实现

select 部门,姓名,薪资 from Test a
where 薪资=(select max(薪资) from test b where a.部门=b.部门)
/***************

=========查询结果===========

部门     姓名     薪资
------ ------ ---------------------
策划部    李波     12000.00
工程部    赵柳     24000.00
市场部    张三     6000.00

(3 行受影响)
****************/

--以上结果无法满足薪资相同的情况

------------------------------------------------------------------------

--2.常规做法：找出每个部门的最大薪资，然后子查询匹配

select test.部门,test.姓名,test.薪资 from Test,
	(select 部门,max(薪资)薪资 from test group by 部门)t 
where Test.部门=t.部门 and test.薪资=t.薪资

/*********

=========查询结果===========

部门     姓名     薪资
------ ------ ----------------
市场部    张三     6000.00
市场部    李四     6000.00
工程部    立白     24000.00
策划部    李波     12000.00

(4 行受影响)
*/
--以上结果正确
---------------------------------------------------------------------------------------------

/*
*以下的分组函数，相对Group by与 compute更好用，用得也比较多，大家可以根据实际情况，自由选择。
*下面我只是做简单介绍，有兴趣的可以看看帮助文档，或者上网查询一下。
*/
--row_number()
/*
*row_number()中的partition by就是按那个字段进行分组,并对分组后的数据进行编号
*如果没有当前字段，那么就是按排序从1开始编号。
*/

--实验
select rowid=row_number() over(order by 薪资 desc),* from Test
select rowid=row_number() over(partition by 部门 order by 薪资 desc),* from Test

/* 以上两条语句查询结果

rowid                部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    工程部    立白     24000.00
2                    策划部    李波     12000.00
3                    工程部    雕牌     10000.00
4                    市场部    张三     6000.00
5                    市场部    李四     6000.00
6                    市场部    王五     5000.00
7                    工程部    赵柳     3400.00
8                    策划部    海丝     2000.00

(8 行受影响)

rowid                部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
2                    策划部    海丝     2000.00
1                    工程部    立白     24000.00
2                    工程部    雕牌     10000.00
3                    工程部    赵柳     3400.00
1                    市场部    张三     6000.00
2                    市场部    李四     6000.00
3                    市场部    王五     5000.00

(8 行受影响)


*/

--通过以上结果我们可以通过rowid来查询，但同样无法解决薪资相同的情况

with t as(
select rowid=row_number() over(partition by 部门 order by 薪资 desc),* from Test)
select * from t where t.rowid=1

/*结果如下：
rowid                部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
1                    工程部    立白     24000.00
1                    市场部    张三     6000.00

(3 行受影响)

*/
--以上结果也未解决同部门同薪资的情况
---------------------------------------------------------------------------
--rank()用法与row_unmber()相同

--注意下面两个查询的区别
select rankId=rank()over(order by 薪资 desc),* from Test
select rankId=rank()over(order by 部门 desc),* from Test

/* 重点看第2个结果
rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    工程部    立白     24000.00
2                    策划部    李波     12000.00
3                    工程部    雕牌     10000.00
4                    市场部    张三     6000.00
4                    市场部    李四     6000.00
6                    市场部    王五     5000.00
7                    工程部    赵柳     3400.00
8                    策划部    海丝     2000.00

(8 行受影响)

rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00
1                    市场部    王五     5000.00
4                    工程部    赵柳     3400.00
4                    工程部    立白     24000.00
4                    工程部    雕牌     10000.00
7                    策划部    海丝     2000.00
7                    策划部    李波     12000.00

(8 行受影响)

*/
--rank() 即是不连续编号的分组函数
select rankId=rank() over(partition by 部门 order by 薪资 desc),* from Test

/* 查询结果
rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
2                    策划部    海丝     2000.00
1                    工程部    立白     24000.00
2                    工程部    雕牌     10000.00
3                    工程部    赵柳     3400.00
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00
3                    市场部    王五     5000.00

(8 行受影响)


--此处需要注意的是，如果同部门同薪资，他们的编号相同，
--比如这里两个薪资为6000的行他们的编号都是“1”，而5000的这一行是“3”而不是“2”。
*/

--通过以上结果很明确的可以知道我们的结果就出来了(能够完美解决相同问题)

with t as(
	select rankId=rank() over(partition by 部门 order by 薪资 desc),* from Test)
select * from t where t.rankid=1

/* 查询结果
rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
1                    工程部    立白     24000.00
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00

(4 行受影响)
*/

--dense_rank()与rank()相反,即他的编号是连续的，可以自己对比一下他们的区别
select drankId=dense_rank()over(order by 薪资),* from Test
select drankId=dense_rank()over(order by 部门),* from Test

/*
drankId              部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    海丝     2000.00
2                    工程部    赵柳     3400.00
3                    市场部    王五     5000.00
4                    市场部    张三     6000.00
4                    市场部    李四     6000.00
5                    工程部    雕牌     10000.00
6                    策划部    李波     12000.00
7                    工程部    立白     24000.00

(8 行受影响)

drankId              部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    海丝     2000.00
1                    策划部    李波     12000.00
2                    工程部    赵柳     3400.00
2                    工程部    立白     24000.00
2                    工程部    雕牌     10000.00
3                    市场部    张三     6000.00
3                    市场部    李四     6000.00
3                    市场部    王五     5000.00

(8 行受影响)

*/
select drankId=dense_rank() over(partition by 部门 order by 薪资 desc),* from Test

with t as(
	select drankId=dense_rank() over(partition by 部门 order by 薪资 desc),* from Test)
select * from t where drankid=1

/*
drankId              部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
1                    工程部    立白     24000.00
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00

(4 行受影响)

*/

--顺便介绍一下ntile() 分组函数，平均分配
--ntile()
select tileid=ntile(3) over(order by 部门),* from Test

[天-笑]

老提示我404 这个要顶！

[问苍天人生几何]

我以前也是用Rank来做

[以学习为目的]

仔细看完了才顶的，SF被抢了

[百年树人]

[Quote=引用楼主 的回复:]
--题目：找出各部门薪资最高的人

--1.常规做法：用group 分组，然后max找出最大值

select 部门,max(姓名) 姓名,max(薪资)薪资 from Test group by 部门

[/Quote]

粗略看了一下，这个语句是错误的(不仅仅是无法满足薪资相同的情况)，因为这种写法，最高的人对应的不一定是max(姓名)
可以用子查询来查
select * from test t where not exists(select 1 from test where 部门=t.部门 and 薪资>t.薪资)

其他的没细看

[Aaron_Chan]

[Quote=引用 5 楼 的回复:]
引用楼主 的回复:
--题目：找出各部门薪资最高的人

--1.常规做法：用group 分组，然后max找出最大值

select 部门,max(姓名) 姓名,max(薪资)薪资 from Test group by 部门



粗略看了一下，这个语句是错误的(不仅仅是无法满足薪资相同的情况)，因为这种写法，最高的人对应的不一定是max(姓名)
可以用子查询来查
select……
[/Quote]

嗯，应该是有这个问题。

[koumingjie]

收藏在看

[开着拖拉机泡妞]

[Quote=引用 6 楼 的回复:]

引用 5 楼 的回复:
引用楼主 的回复:
--题目：找出各部门薪资最高的人

--1.常规做法：用group 分组，然后max找出最大值

select 部门,max(姓名) 姓名,max(薪资)薪资 from Test group by 部门



粗略看了一下，这个语句是错误的(不仅仅是无法满足薪资相同的情况)，因为这种写法，最高的人对应的不一定是max(姓名)
可……
[/Quote]

周末出来喝水

[Aaron_Chan]

[Quote=引用 8 楼 的回复:]
引用 6 楼 的回复:

引用 5 楼 的回复:
引用楼主 的回复:
--题目：找出各部门薪资最高的人

--1.常规做法：用group 分组，然后max找出最大值

select 部门,max(姓名) 姓名,max(薪资)薪资 from Test group by 部门



粗略看了一下，这个语句是错误的(不仅仅是无法满足薪资相同的情况)，因为这种写法，最高的人对应的不……
[/Quote]
看来，周末不用做饭了。

[manxiSafe]

总要写10个字吧，不然它不给我回复分。
其实，我是路过。