如何获取当前行的下一行值？

问题描述：

 EmployeeTable
-------------------------------
EmpId	EmpName	Salary
1	A       100
2	B       101
3	C       102
4	D       103
5	E       104
6	F       105
7	G       106
8	H       107

--------------------------- -----

--------------------------------

How to write a query to get below output, to get 2nd row value in 1st row?
------------
EmpId	EmpName	Salary EmpName2  Salary2
1	A       100      B         101
2	B       101      C         102
3	C       102      D         103
4	D       103      E         104
5	E       104      F         105
6	F       105      G         106
7	G       106      H         107
8	H       107      I

请给我一个获得此输出的方法。

Please give me a way to get this output.

答

如果你在一对夫妇中这样做，最容易理解步骤，但你可以使用连接到派生表而不是使用临时表，并在单个查询中获得结果。

一些原始数据，例如

It is easiest to understand if you do it in a couple of steps, but you could use a join to a derived table rather than using a temporary table and get the result in a single query.

Some raw data for example

id          name
----------- ----------
120         name 120
125         name 125
131         name 131
133         name 133
165         name 165
765         name 765

首先要做的就是完成订单。

First thing to do is work out the order.

-- Get a chain of employee IDs. Use min & grouping so it doesn't
-- matter if there are gaps in the ID sequence.
select 
  employee.id, 
  min(coalesce(next.ID, 0)) as [next]
into #ascendingOrder
from employee
left join employee as next 
      on next.id > employee.id
group by employee.id

-- Show that we've got the ordered IDs
select * from #ascendingOrder

id          next
----------- -----------
120         125
125         131
131         133
133         165
165         765
765         0

一旦我们'得到了我们的订单，提取很简单。

Once we've got our ordering the extraction is straight-forward.

-- Extract the data 
select 
  employee.id,
  employee.name, 
  coalesce([next].id,0) as [nextID], 
  coalesce([next].name,'') as [nextName]
from employee
  left join #ascendingOrder on #ascendingOrder.id = employee.id
  left join employee as [next] on [next].id = #ascendingOrder.next

id          name       nextID      nextName
----------- ---------- ----------- ----------
120         name 120   125         name 125
125         name 125   131         name 131
131         name 131   133         name 133
133         name 133   165         name 165
165         name 165   765         name 765
765         name 765   0

试用这：

Try this:

SELECT t1.EmpId, t1.EmpName, t1.Salary, t2.EmpId AS EmpId2, t2.EmpName AS EmpName2, t2.Salary AS Salary2
FROM EmployeeTable AS t1 INNER JOIN EmployeeTable AS t2 ON t1.EmpId = t2.EmpId +1

它应该产生适合您需要的结果。

It should produce result suitable to your needs.

如何获取当前行的下一行值？

相关推荐