减去上一行id与上一行相同的数据

本文介绍了减去上一行id与上一行相同的数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我整个下午都在努力尝试实现这一目标，但没有成功.

I have been trying all afternoon to try and achieve this with no success.

我有一个数据库，其中包含有关客户的信息以及他们从商店购买产品的日期.它按批次 ID 分组，我已将其转换为日期格式.

I have a db in with info on customers and the date that they purchase products from the store. It is grouped by a batch ID which I have converted into a date format.

所以在我的表中我现在有:

So in my table I now have:

CustomerID|Date 1234 |2011-10-18 1234 |2011-10-22 1235 |2011-11-16 1235 |2011-11-17

我想要实现的是查看最近一次购买和最后一次购买之间的天数等等.

What I want to achieve is to see the number of days between the most recent purchase and the last purchase and so on.

例如:

CustomerID|Date |Outcome 1234 |2011-10-18 | 1234 |2011-10-22 | 4 1235 |2011-11-16 | 1235 |2011-11-17 | 1

我尝试将表格加入到自己的表格中，但我遇到的问题是我最终以相同的格式加入.然后我尝试使用我的 join 语句返回它的位置 <> 匹配日期.

I have tried joining the table to itself but the problem I have is that I end up joining in the same format. I then tried with my join statement to return where it did <> match date.

希望这是有道理的，任何帮助表示赞赏.我已经搜索了此处的所有相关主题.

Hope this makes sense, any help appreciated. I have searched all the relevant topics on here.

推荐答案

CustomerID 会有多组吗?或者只是并且总是组合在一起?

Will there be multiple groups of CustomerID? Or only and always grouped together?

DECLARE @myTable TABLE ( CustomerID INT, Date DATETIME ) INSERT INTO @myTable SELECT 1234, '2011-10-14' UNION ALL SELECT 1234, '2011-10-18' UNION ALL SELECT 1234, '2011-10-22' UNION ALL SELECT 1234, '2011-10-26' UNION ALL SELECT 1235, '2011-11-16' UNION ALL SELECT 1235, '2011-11-17' UNION ALL SELECT 1235, '2011-11-18' UNION ALL SELECT 1235, '2011-11-19' SELECT CustomerID, MIN(date), MAX(date), DATEDIFF(day,MIN(date),MAX(date)) Outcome FROM @myTable GROUP BY CustomerID SELECT a.CustomerID, a.[Date], ISNULL(DATEDIFF(DAY, b.[Date], a.[Date]),0) Outcome FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row, CustomerID, Date FROM @myTable ) A LEFT JOIN ( SELECT ROW_NUMBER() OVER(PARTITION BY [CustomerID] ORDER BY date) Row, CustomerID, Date FROM @myTable ) B ON a.CustomerID = b.CustomerID AND A.Row = B.Row + 1