如何与声明

Question

我要寻找一个在SQL一些帮助创建使用SQL Server中的标识列，我使用下面的查询

with t AS 
(
    select 
     EmpID, mgrid, HierarchyLevel, Description 
    from 
     empdatatest 
) 
select * 
from t 
order by empid 

我想办法让表T有标识列

数据输出应该像

ID EmpID mgrid HierarchyLevel Description 
------------------------------------------ 
1 201 7  1   Partner 
2 202 201  2   Senior Manager 
3 221 202  3   Manager 
4 343 221  4   employee 
5 534 221  4   employee 
6 552 221  4   employee 

Answer 1

使用ROW_NUMBER()：

;With t As 
(
    Select Row_Number() Over (Order By EmpId) As ID, 
      EmpID, 
      mgrid, 
      HierarchyLevel, 
      Description 
    From empdatatest 
) 
Select * 
From t 
Order By empid;