SQL查询：获取列中的前一个日期

Question

我有一个在SQL服务器中的表格，其中包含一些日期。现在我想创建一个查询语句，使其返回一个列包含所有日期，第二个列包含第一个列的前一个日期，第三个列包含第二列的前一个日期(c2)。 例如:

  c1(orginal)          c2(prevoius of c1)    c3(previous of c2)
2017-10-15 00:00:00   2017-04-15 00:00:00   2016-10-15 00:00:00
2017-04-15 00:00:00   2016-10-15 00:00:00   2016-04-15 00:00:00
2016-10-15 00:00:00   2016-04-15 00:00:00   2015-10-15 00:00:00
2016-04-15 00:00:00   2015-10-15 00:00:00           null
2015-10-15 00:00:00           null                  null

颜色示例：

是否可以创建一个 SELECT 语句，其中第一行将是列1中的第一个日期，第二行将是列1中的第二个日期，以此类推。第二行将是列1中的第二个日期，第三行将是列1中的第三个日期，以此类推。

我的当前查询：

SELECT DISTINCT(BFSSTudStichdatum) AS C1, BFSSTudStichdatum AS C2, 
BFSSTudStichdatum AS C3  FROM BFSStudierende
ORDER BY C1 DESC

结果：

翻译：无。

- oibaf

---

你已经尝试过哪些查询？ - Hintham

你的意思是在column2中找到column1的最大日期吗？你的问题中提到了所有以前的日期，这意味着你的结果集会变得非常庞大。 - Ab Bennett

你的数据是否可能包含重复项？如果是，那么应该消除这些重复项吗？ - Ab Bennett

Answer 1

你正在寻找lag()函数吗？

select col1,
       lag(col1, 1) over (order by col1) as col1_prev,
       lag(col1, 2) over (order by col1) as col1_prev2
from t;

- Gordon Linoff

---

如果存在重复的日期，那么第二个时间差不能确保第三个日期比第二个日期小。 - Ab Bennett

我在问题中没有看到任何涉及该问题的内容。你应该询问提问者（1）是否存在重复项，以及（2）如何处理它们。我认为没有必要使问题变得复杂。 - Gordon Linoff

没有这个信息，你不能假设没有重复。 - Ab Bennett

@AbBennett……也许你不理解这个问题，但对我来说图片非常清晰。我没有看到任何歧义。你应该问一下提问者。 - Gordon Linoff

如果你没有询问过这个人或与他们核实，那么图片是如何清晰的呢？ - Ab Bennett

Answer 2

因为您需要先获取日期的distinct列表，所以您需要将查询拆分成一个公共表达式，然后使用lag来获取c2和c3值:

declare @t table(c1 datetime);
insert into @t values ('2017-10-15 00:00:00'),('2017-04-15 00:00:00'),('2016-10-15 00:00:00'),('2016-04-15 00:00:00'),('2015-10-15 00:00:00')
                     ,('2017-10-15 00:00:00'),('2017-04-15 00:00:00'),('2016-10-15 00:00:00'),('2016-04-15 00:00:00'),('2015-10-15 00:00:00');

with c as
(
    select distinct c1
    from @t
)
select c1
      ,lag(c1, 1) over (order by c1) as c2
      ,lag(c1, 2) over (order by c1) as c3
from c
order by c1 desc;

输出：

+-------------------------+-------------------------+-------------------------+
|           c1            |           c2            |           c3            |
+-------------------------+-------------------------+-------------------------+
| 2017-10-15 00:00:00.000 | 2017-04-15 00:00:00.000 | 2016-10-15 00:00:00.000 |
| 2017-04-15 00:00:00.000 | 2016-10-15 00:00:00.000 | 2016-04-15 00:00:00.000 |
| 2016-10-15 00:00:00.000 | 2016-04-15 00:00:00.000 | 2015-10-15 00:00:00.000 |
| 2016-04-15 00:00:00.000 | 2015-10-15 00:00:00.000 | NULL                    |
| 2015-10-15 00:00:00.000 | NULL                    | NULL                    |
+-------------------------+-------------------------+-------------------------+

- iamdave

---

这些滞后值并不能保证数值比前一个日期小，如果同一日期重复多次。 - Ab Bennett

如果是这样的话，那么你也给出了负面评价的Gordon的答案就是正确的。然而，考虑到OP当前查询中包含了“distinct”，我会进一步假设他们想要一个唯一日期的集合。 - iamdave

它从最终数据集中消除了重复的日期，但我认为distinct是危险的，并且会产生错误的结果。我不做任何假设，无论它们是否唯一或者如果它们应该被消除。如果你有大量的行，我的方法可能效率较低，但对于小数据来说足够快。在最坏的情况下，索引列。 - Ab Bennett

@AbBennett，幸好你不是原帖作者，不是吗？ - iamdave

@AbBennett，你会注意到这已经被接受为正确答案，我认为这相当决定性地解决了问题。很高兴你对在这里提供答案如此热衷，但请只在你有合适的答案时提供答案，并且不要因为发布者不同意你而变得如此激动 - 我的意思是，给有用的答案投反对票，真的吗？ - iamdave

Answer 3

对于 SQL Server 2008 及更高版本：

WITH DataSource AS
(
    SELECT DISTINCT *
          ,DENSE_RANK() OVER (ORDER BY c1) rowID
    FROM @t
)
SELECT DS1.[c1]
      ,DS2.[c1]
      ,DS3.[c1]
FROM DataSource DS1
LEFT JOIN DataSource DS2
    ON DS1.[rowID] = DS2.[rowID] + 1
LEFT JOIN DataSource DS3
    ON DS1.[rowID] = DS3.[rowID] + 2;

- gotqn

Answer 4

针对 SQL Server 2008 及更高版本：

希望您想要一个自动生成列，其值始终落后于过去列的第一个值。请尝试以下代码片段。

根据数据集中的列数创建动态查询。

create table BFSStudierende
(
BFSSTudStichdatum  datetime
)
insert into BFSStudierende
Select getdate()
union
Select dateadd(day,1,getdate())
union
Select dateadd(day,2,getdate())
union
Select dateadd(day,3,getdate())
union
Select dateadd(day,4,getdate())

Declare @count int=(Select count(BFSSTudStichdatum ) from BFSStudierende)

Declare @query nvarchar(max)='with BFSStudierendeCte as (Select *,row_number() over(order by BFSSTudStichdatum)rn  from BFSStudierende)  Select *from BFSStudierendeCte as BFSStudierendeCte1'

Declare  @i int=2 ;
Declare  @j int ;

while(@i<=@count)
begin
Set @j=@i-1
Set @query=@query+' left outer join BFSStudierendeCte as BFSStudierendeCte'+cast(@i as varchar(5)) +' on BFSStudierendeCte1.rn+'+cast(@j as varchar(5))+'=BFSStudierendeCte'+cast(@i as varchar(5))+'.rn';
set @i+=1;
End
print @query
Execute(@query)

注意：结果中不会删除重复的数据。如果您需要删除重复项，请更改上面代码段中的以下行。

Declare @count int=(Select count(distinct BFSSTudStichdatum ) from BFSStudierende)

Declare @query nvarchar(max)='with BFSStudierendeCte as (Select *,row_number() over(order by BFSSTudStichdatum)rn from(Select distinct BFSSTudStichdatum from BFSStudierende)l   )  Select *from BFSStudierendeCte as BFSStudierendeCte1'

- JayaPrakash