如何获取当前行的下一行值? [英] How to get next row value with current row ?
本文介绍了如何获取当前行的下一行值?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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.
推荐答案
如果你在一对夫妇中这样做,最容易理解步骤,但你可以使用连接到派生表而不是使用临时表,并在单个查询中获得结果。
一些原始数据,例如< br $>
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.
>
这篇关于如何获取当前行的下一行值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文