如何在表上显示逻辑ID(How to Show logical Ids on table)

如何在表上显示逻辑ID(How to Show logical Ids on table)

我有员工表和他们的楼层如图所示：

ID EmployeeId EmpName FloorId 1 abc123 abc 1 2 xyz123 xyz 1 3 def123 def 2 4 pqr123 pqr 2

我在地板明智的基础上显示了表的结果，但是在表示2楼员工的时候想要结果这样显示：

select * from tblname where FloorId=2

ID EmployeeId EmpName FloorId 1 def123 def 2 2 pqr123 pqr 2

I have table of employees and their floor as shown:

ID EmployeeId EmpName FloorId 1 abc123 abc 1 2 xyz123 xyz 1 3 def123 def 2 4 pqr123 pqr 2

I have shown result from table on floor wise basis,but when I shows 2nd floor employees I want result to be shown like this:

select * from tblname where FloorId=2

ID EmployeeId EmpName FloorId 1 def123 def 2 2 pqr123 pqr 2

最满意答案

您可以使用ROW_NUMBER() 。 它返回结果集分区中行的序号，从1开始，每个分区的第一行。

试试这个：

SELECT ROW_NUMBER() OVER(ORDER BY ID) as ID,EmployeeId,EmpName,FloorId FROM tableName WHERE FloorId=2

结果如下：

ID EMPLOYEEID EMPNAME FLOORID 1 def123 def 2 2 pqr123 pqr 2

在SQL Fiddle中查看结果。

在这里阅读更多关于ROW_NUMBER() 信息 。

You can use ROW_NUMBER(). It returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Try this:

SELECT ROW_NUMBER() OVER(ORDER BY ID) as ID,EmployeeId,EmpName,FloorId FROM tableName WHERE FloorId=2

The result will be like:

ID EMPLOYEEID EMPNAME FLOORID 1 def123 def 2 2 pqr123 pqr 2

See result in SQL Fiddle.

Read more about ROW_NUMBER() here.