SQL Server - 自動產生編號

create table #t1
(
    salary_month varchar(6),
    emp_no varchar(5),
    salary money
)
go
insert into #t1 (salary_month,emp_no,salary) values('201201','A0001',10000)
insert into #t1 (salary_month,emp_no,salary) values('201202','A0001',15000)
insert into #t1 (salary_month,emp_no,salary) values('201201','A0002',30000)
insert into #t1 (salary_month,emp_no,salary) values('201201','A0003',20000)
insert into #t1 (salary_month,emp_no,salary) values('201201','A0004',50000)
insert into #t1 (salary_month,emp_no,salary) values('201202','A0004',52000)
insert into #t1 (salary_month,emp_no,salary) values('201203','A0004',51000)
go
select * from #t1
go
--依照每筆排序
select ROW_NUMBER() OVER(ORDER BY emp_no) AS id,salary_month,emp_no,salary
from #t1

go
--依照每筆排序(依照群組跳號)
select RANK() OVER(ORDER BY emp_no) AS id,salary_month,emp_no,salary
from #t1

go
--依照每筆排序(依照群組不跳號)
select DENSE_RANK() OVER(ORDER BY emp_no) AS id,salary_month,emp_no,salary
from #t1

go

--依照每筆排序(依照群組排序)
select ROW_NUMBER() OVER(PARTITION BY emp_no order by salary_month asc ) AS id,salary_month,emp_no,salary
from #t1