sqlserver问题。求思路

发布网友发布时间：2022-12-21 19:56

共4个回答

热心网友时间：2024-07-01 23:22

select t1.uid,datediff(dd,t1.adate,t2.adate) from
(select * from test a where (select count(*) from test b where a.uid=b.uid and b.adate<a.adate) between 1 and 2) t1,
(select * from test a where (select count(*) from test b where a.uid=b.uid and b.adate<a.adate) between 1 and 2) t2
where t1.uid=t2.uid and t2.adate>t1.adate

表名test，自己换一下

热心网友时间：2024-07-01 23:22

select uid,
datediff(day,max(case when MYID = 3 then adate else null end),
max(case when MYID = 2 then adate else null end)) as 间隔天数
from
(select ROW_NUMBER() OVER(PARTITION BY UID ORDER BY adate DESC) as MYID,adate,uid from test) a
group by uid

热心网友时间：2024-07-01 23:23

;with cte as(select *,row_number() over(partition by uid order by adate)rn from tb)
select a.*,b.*,datediff(day,a.adate,b.adate) from cte a,cte b where a.uid=b.uid and a.rn=2 and b.rn=3

热心网友时间：2024-07-01 23:24

假设表名是：table

select uid,
(select top 1 t2.adate from(select top 2 adate from table t1 where t1.uid=t.uid order by t1.adate asc) t2 order by desc) min2,
(select top 1 t3.adate from(select top 3 adate from table t4 where t4.uid=t.uid order by t4.adate asc) t3 order by desc) min2,
from table t group by uid