我想要一个SQL查询将第二行数据显示为第一行，将第三行数据显示为第二行，依此类推 [英] I want a sql query to show the second row data into first row and third row data into second row and so on

问题描述

我的sql server中有一个表。该表有一列。



示例表名：示例（列名：数据）

 7/11/2014 12:00:00 AM 
 7/31/2014 12:00:00 AM 
 8/21/2014 12:00： 00 AM 
 9/19/2014 12:00:00 AM 
 11/20/2014 12:00:00 AM 
 12/18/2014 12:00:00 AM 
 1/22/2015 12:00:00 AM 

因此，根据这些数据，我想在该列中添加一个额外的列我希望第二行数据进入第一行，第三行数据进入第二行......依此类推。



示例：

 7/11/2014 12:00:00 AM 7/31/2014 12:00:00 AM 
 7/31/2014 12:00:00 AM 8/21/2014 12： 00:00 AM 
 8/21/2014 12:00:00 AM 9/19/2014 12:00:00 AM 
 9/19/2014 12:00:00 AM 11/20/2014 12:00:00 AM 
 11/20/2014 12:00:00 AM 12/18/2014 12:00:00 AM 
 12/18/2014 12:00:00 AM 1/22 / 2015 12:00:00 AM 
 1/22/2015 12:00:00 AM Null 

向我提供如何获得预期产量的建议。



我尝试过：



告诉我如何获得预期输出的建议。

解决方案

如果您正在使用最新版本的SQL Server（从2012年但不是Express）然后你可以使用 LEAD（Transact-SQL） [ ^ ]功能。



它应该类似于

  SELECT  [data]，LEAD（[数据]， 1 ） OVER （ ORDER   BY  [data]） AS  nxt 
  FROM 示例

（警告 - 我没有检查过，因为不能到目前为止获得SQLFiddle）



如果您有早期版本的SQL，那么尝试使用基于Row进行自连接的公用表表达式（CTE） number ..

;  WITH  CTE  AS  
（
 选择 
 ROW_NUMBER（） OVER （ ORDER   BY  data） as  rn，
 data 
 来自示例
）
  SELECT  base.data， nxt.data 
 来自 CTE基础
  LEFT   OUTER   JOIN  CTE nxt  on  nxt.rn = base.rn +  1  

又一种变体（这是我几年前写的那些应该在没有LEAD和CTE的地方工作。



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

I have a table in my sql server.That table has one column.

Example table name : Sample (column name: data)

7/11/2014 12:00:00 AM
7/31/2014 12:00:00 AM
8/21/2014 12:00:00 AM
9/19/2014 12:00:00 AM
11/20/2014 12:00:00 AM
12/18/2014 12:00:00 AM
1/22/2015 12:00:00 AM

So by this data I want to add an extra column and in that column I want the second row data into first row, third row data into second row ...so on.

Example:

7/11/2014 12:00:00 AM        7/31/2014 12:00:00 AM
7/31/2014 12:00:00 AM        8/21/2014 12:00:00 AM
8/21/2014 12:00:00 AM        9/19/2014 12:00:00 AM                        
9/19/2014 12:00:00 AM        11/20/2014 12:00:00 AM
11/20/2014 12:00:00 AM       12/18/2014 12:00:00 AM
12/18/2014 12:00:00 AM       1/22/2015 12:00:00 AM
1/22/2015 12:00:00 AM        Null   

Provide me the suggestions how I will get my expected output.

What I have tried:

Provide me the suggestions how I will get my expected output.

解决方案

If you are using the latest versions of SQL Server (from 2012 but not Express) then you can use the LEAD (Transact-SQL)[^] function.

It should be something like

SELECT [data], LEAD([data], 1) OVER (ORDER BY [data]) AS nxt
FROM Sample

(Caveat - I haven't checked this as can't get to SQLFiddle at the moment)

If you have an earlier version of SQL then try using a Common Table Expression (CTE) which does a self-join based on Row number..

;WITH CTE AS
(
	select 
	ROW_NUMBER() OVER(ORDER BY data) as rn,
	data
	from sample
)
SELECT base.data, nxt.data
from CTE base
LEFT OUTER JOIN CTE nxt on nxt.rn = base.rn + 1

Yet another variation (which I scribbled a couple of years ago) that should work where LEAD and CTEs aren't available.

How to get next row value with current row ?[^]

这篇关于我想要一个SQL查询将第二行数据显示为第一行，将第三行数据显示为第二行，依此类推的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！