1.row_number
2.rank
3.dense_rank
4.ntile
row_number
The row_number function has a very wide range of uses, and its function is to generate a serial number for each row of records queried.
The row_number column is an ordinal column generated by the row_number function. The row_number function is used to select and sort a column using the over clause before generating the ordinal number.
Two, rank
The ordinal number generated by rank function may be discontinuous
dense_rank
The function of the dense_rank function is similar to that of the rank function, except that the sequence number generated by the rank function is continuous, while the sequence number generated by the rank function may be discontinuous.
TILE
The ntile function can group serial numbers. This is equivalent to putting the queried recordset into an array of specified length, and each element of the array stores a certain number of records. The ordinal number generated by the ntile function for each record is the index of all array elements of that record (starting from 1). An array element for each allocated record can also be called a bucket. The ntile function has a parameter that specifies the number of buckets.
create table Test
(
//Department char(6),
//Name varchar(6)
//Salary money
)
go
insert into Test values('Marketing Department','Zhang San',6000)
insert into Test values('Marketing Department','Li Si',6000)
insert into Test values('Marketing Department','Wang Wu',5000)
insert into Test values('Engineering Department','Zhao Liu',3400)
insert into Test values('Engineering Department','Liby',24000)
insert into Test values('Engineering Department','Diaopai',10000)
insert into Test values('Planning Department','Hayes',2000)
insert into Test values('Planning Department','Li Bo',12000)
go
--Topic: Find out the highest-paid people in all departments
--1.Conventional approach: implementation of related sub-queries
select department,Full name,salary from Test a
where salary=(select max(salary) from test b where a.department=b.department)
/***************
=========Query result==================
Department name salary
------ ------ ---------------------
Planning Department Libo 12000.00
Zhaoliu 24000.00, Ministry of Engineering
Market Department Zhang San 6000.00
(3 Action affected)
****************/
--The above results do not meet the same salary situation.
------------------------------------------------------------------------
--2.Conventional practice: Find out the maximum salary for each department, and then subquery matching
select test.department,test.Full name,test.salary from Test,
(select department,max(salary)salary from test group by department)t
where Test.department=t.department and test.salary=t.salary
/*********
=========Query result==================
Department name salary
------ ------ ----------------
Market Department Zhang San 6000.00
Li Si 6000.00, Marketing Department
Libai 24000.00, Ministry of Engineering
Planning Department Libo 12000.00
(4 Action affected)
*/
--The above results are correct.
---------------------------------------------------------------------------------------------
/*
*The following grouping functions are better and more useful than Group by and Computer. We can choose them freely according to the actual situation.
*Next, I just give a brief introduction. If you are interested, you can look at the help documents or search the internet.
*/
--row_number()
/*
*row_number()The partition by in the grouping is grouped according to that field, and the grouped data is numbered.
*If there is no current field, it is numbered from 1 in order.
*/
--Experiment
select rowid=row_number() over(order by salary desc),* from Test
select rowid=row_number() over(partition by department order by salary desc),* from Test
/* The query results of the above two statements
rowid Department name salary
-------------------- ------ ------ ---------------------
1 Libai 24000.00, Ministry of Engineering
2 Planning Department Libo 12000.00
3 Engineering Department carved 10000.00
4 Market Department Zhang San 6000.00
5 Li Si 6000.00, Marketing Department
6 Wang Wu5000.00, Marketing Department
7 Zhaoliu 3400.00, Ministry of Engineering
8 Planning Department Hess 2000.00
(8 Action affected)
rowid Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Libo 12000.00
2 Planning Department Hess 2000.00
1 Libai 24000.00, Ministry of Engineering
2 Engineering Department carved 10000.00
3 Zhaoliu 3400.00, Ministry of Engineering
1 Market Department Zhang San 6000.00
2 Li Si 6000.00, Marketing Department
3 Wang Wu5000.00, Marketing Department
(8 Action affected)
*/
--Through the above results we can pass rowid To query, but also can not solve the same salary situation
with t as(
select rowid=row_number() over(partition by department order by salary desc),* from Test)
select * from t where t.rowid=1
/*The results are as follows:
rowid Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Libo 12000.00
1 Libai 24000.00, Ministry of Engineering
1 Market Department Zhang San 6000.00
(3 Action affected)
*/
--The above results also do not solve the same salary situation in the same department.
---------------------------------------------------------------------------
--rank()Usage and row_unmber()identical
--Notice the difference between the following two queries
select rankId=rank()over(order by salary desc),* from Test
select rankId=rank()over(order by department desc),* from Test
/* Focus on the second result
rankId Department name salary
-------------------- ------ ------ ---------------------
1 Libai 24000.00, Ministry of Engineering
2 Planning Department Libo 12000.00
3 Engineering Department carved 10000.00
4 Market Department Zhang San 6000.00
4 Li Si 6000.00, Marketing Department
6 Wang Wu5000.00, Marketing Department
7 Zhaoliu 3400.00, Ministry of Engineering
8 Planning Department Hess 2000.00
(8 Action affected)
rankId Department name salary
-------------------- ------ ------ ---------------------
1 Market Department Zhang San 6000.00
1 Li Si 6000.00, Marketing Department
1 Wang Wu5000.00, Marketing Department
4 Zhaoliu 3400.00, Ministry of Engineering
4 Libai 24000.00, Ministry of Engineering
4 Engineering Department carved 10000.00
7 Planning Department Hess 2000.00
7 Planning Department Libo 12000.00
(8 Action affected)
*/
--rank() That is, grouping functions with discontinuous numbers
select rankId=rank() over(partition by department order by salary desc),* from Test
/* Query results
rankId Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Libo 12000.00
2 Planning Department Hess 2000.00
1 Libai 24000.00, Ministry of Engineering
2 Engineering Department carved 10000.00
3 Zhaoliu 3400.00, Ministry of Engineering
1 Market Department Zhang San 6000.00
1 Li Si 6000.00, Marketing Department
3 Wang Wu5000.00, Marketing Department
(8 Action affected)
--It should be noted here that if the same department is paid the same number.
--For example, two lines with a salary of 6000 are numbered "1", while the line with a salary of 5000 is "3" instead of "2".
*/
--From the above results, it is clear that our results will come out.(Be able to solve the same problem perfectly)
with t as(
select rankId=rank() over(partition by department order by salary desc),* from Test)
select * from t where t.rankid=1
/* Query results
rankId Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Libo 12000.00
1 Libai 24000.00, Ministry of Engineering
1 Market Department Zhang San 6000.00
1 Li Si 6000.00, Marketing Department
(4 Action affected)
*/
--dense_rank()and rank()Contrary,That is, his number is continuous, you can compare their differences.
select drankId=dense_rank()over(order by salary),* from Test
select drankId=dense_rank()over(order by department),* from Test
/*
drankId Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Hess 2000.00
2 Zhaoliu 3400.00, Ministry of Engineering
3 Wang Wu5000.00, Marketing Department
4 Market Department Zhang San 6000.00
4 Li Si 6000.00, Marketing Department
5 Engineering Department carved 10000.00
6 Planning Department Libo 12000.00
7 Libai 24000.00, Ministry of Engineering
(8 Action affected)
drankId Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Hess 2000.00
1 Planning Department Libo 12000.00
2 Zhaoliu 3400.00, Ministry of Engineering
2 Libai 24000.00, Ministry of Engineering
2 Engineering Department carved 10000.00
3 Market Department Zhang San 6000.00
3 Li Si 6000.00, Marketing Department
3 Wang Wu5000.00, Marketing Department
(8 Action affected)
*/
select drankId=dense_rank() over(partition by department order by salary desc),* from Test
with t as(
select drankId=dense_rank() over(partition by department order by salary desc),* from Test)
select * from t where drankid=1
/*
drankId Department name salary
-------------------- ------ ------ ---------------------
1 Planning Department Libo 12000.00
1 Libai 24000.00, Ministry of Engineering
1 Market Department Zhang San 6000.00
1 Li Si 6000.00, Marketing Department
(4 Action affected)
*/
--By the way, let me introduce it. ntile() Grouping function, average allocation
--ntile()
select tileid=ntile(3) over(order by salary desc),* from Test1. Ministry of Engineering, Libai 24000.00 1. Planning Department Libo 12000.00 1. Engineering Department 10 000.00 carved cards 2. Marketing Department Zhang San 6000.00 2. Marketing Department Lisi 6000.00 2. Marketing Department Wang Wu 5000.00 3. Ministry of Engineering Zhao Liu 3400.00 3. Planning Department, Hess 2000.00