对比两个时间间隔的SQL语句如何写？

[l171147904]

数据：
客户 购买时间
李 2014-4-8 12:11:00
李 2014-4-2 11:00:00
李 2014-4-1 11:00:00
王 2014-4-4 12:00:00
王 2014-4-3 13:00:00
张 2014-4-1 09:00:00


查询：第一次购买与第二购买时间间隔1天以内客户（时间可以无视，直接天数相差1天即可）

结果：
李
王

[sj490790083]

DATEDIFF(day,' 2014-4-2 11:00:00','2014-4-1 11:00:00')函数 select row_number() over (partition by 客户 order by 购买时间) as index 然后比较index 为0和1的时间差

[好基友一被子]

DATEDIFF函数

[md5e]

Select [name],[time],[value2] From(
Select [name],[time],sum([value]) OVER(PARTITION by [name],rnum) as value2 From (
Select [name],[time],[value],((row_number() OVER(PARTITION by [name] Order by [time] DESC,[value]))+1)/2 as rnum From (
select [name],[time],datediff(dd,[time],getdate()) as [value] From test
Union all
select [name],[time],-datediff(dd,[time],getdate()) as [value] From(
select [name],[time],row_number() OVER(PARTITION by [name] Order by [time] DESC) as rnum From test
) as tb1 Where rnum>1
) tb2
) tb3
) tb4 Where value2=-1 Order by [time]

[md5e]

其实SQL实现很不效率,最好通过DataTable来操作,选排序 for(int i=0;i<DataTable.Rows.Count-1;i++){ if(row[i]["name"].ToString()==row[i+1]["name"].ToString()){ 比较时间,如果相减==1并且不在已存在的记录中,就记录到另一个DataTable中或是List中 } }

[l171147904]

用： lead(时间) over (PARTITION by 客户 order by 时间 实现谢谢了。