如何在表上显示逻辑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楼员工的时候想要结果这样显示:
ID EmployeeId EmpName FloorId 1 def123 def 2 2 pqr123 pqr 2select * from tblname where FloorId=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 2I have shown result from table on floor wise basis,but when I shows 2nd floor employees I want result to be shown like this:
ID EmployeeId EmpName FloorId 1 def123 def 2 2 pqr123 pqr 2select * from tblname where FloorId=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=2The result will be like:
ID EMPLOYEEID EMPNAME FLOORID 1 def123 def 2 2 pqr123 pqr 2See result in SQL Fiddle.
Read more about ROW_NUMBER() here.
更多推荐
发布评论