如何使用MSSQL获取同一行中的当前行和下一行值 [英] How to fetch current and next row value in same row using MSSQL

问题描述

我是SQL的新手并遇到了一个问题，我必须以下面的方式获取值：

现有数据表

I am new to SQL and stuck in a problem where i have to fetch values in below way:
existing datatable

ID | Date
01 | 3/3/17
02 | 4/3/17
03 | 8/3/17
04 | 1/4/17

我想要以下面的方式选择数据：

I want the data should be selected in below way:

ID  | Date1  | Date2
01  | 3/3/17 | 4/3/17 
02  | 4/3/17 | 8/3/17
03  | 8/3/17 | 1/4/17
04  | 1/4/17 | null

请指导我在上面选择SQL查询。



我尝试过：



来自我试过的一些链接：

Please guide me SQL query to select above way.

What I have tried:

from some link I tried:

SELECT A.Id , 
               A.Date AS CurDate , 
               B.Date AS NxtDate 
FROM words AS A
LEFT JOIN words AS B ON A.Date < B.Date 

（ID将是GUID，所以我没有在任何情况下都考虑Id）

这不是预期的结果..

(ID will be GUID, so I have not consider Id in any condition)
This is not giving expected result..

推荐答案

查看您的预期数据它看起来像是在日期订单。您可以在此基础上分配行号，以替换解决方案1中使用的ID（因此可以使用GUID作为ID），例如

Looking at your expected data it would appear to be in Date order. You can assign a row number on that basis which replaces the ID used in Solution 1 (and would therefore work with a GUID as the ID) e.g.

DECLARE @Table TABLE (ID INT IDENTITY(1,1) NOT NULL, TheDate DATE)

INSERT @Table(TheDate) 
values ('03-MAR-2017'), ('04-MAR-2017'), ('08-MAR-2017'), ('01-APR-2017')
;WITH CTE AS
(
    SELECT ID, TheDate
    ,ROW_NUMBER() OVER(ORDER BY TheDate) AS rn
    FROM @Table
)
SELECT CTE.ID, CTE.TheDate As Date1, NXT.TheDate As Date2
FROM CTE
LEFT OUTER JOIN CTE NXT ON NXT.rn = CTE.rn + 1

如果您有SQL Server 2012（不是Express版本）或更高版本（包括2014 Express），那么您可以使用Window函数（远neater）

If you have SQL Server 2012 (not the Express version) or a later version (including 2014 Express), then you can use Window functions instead (far neater)

SELECT ID, TheDate, LEAD(TheDate,1) OVER (ORDER BY TheDate) FROM @Table

这适用于您当前的数据;但是，如果你切换到GUID，你就可以自己了。

This works with your current data; however, you are on your own if you switch to a GUID

DECLARE @Table TABLE (ID INT IDENTITY(1,1) NOT NULL, TheDate DATE)

INSERT @Table(TheDate) 
values ('03/03/2017'), ('04/03/2017'), ('08/03/2017'), ('01/04/2017')

SELECT a.ID
     , Date1 = a.TheDate
     , Date2 = b.TheDate

FROM            @Table  a
LEFT OUTER JOIN @Table  b ON a.ID + 1 = b.ID

ORDER BY A.ID

这篇关于如何使用MSSQL获取同一行中的当前行和下一行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！