34,593
社区成员
发帖
与我相关
我的任务
分享
;with t1 as(
select * from tab a where not exists(select 1 from tab where senid=a.senid and minv>a.minv)
),t2
as(
select * from tab a where not exists(select 1 from tab where senid=a.senid and maxv<a.mav)
)
select isnull(t1.senid,t2.senid) as senid,t1.minv,t1.mint,t2.maxv.t2.maxt
from t1
full join t2
on t1.senid=t2.senid
;with cte as(
select *,rn1=ROW_NUMBER() over(partition by senid order by minv asc),rn2=ROW_NUMBER() over(partition by senid order by maxv desc) from tb
)
select senid,min(minv)as minv,max(maxv) as maxv,max(case when rn1=1 then mint else '1900-01-01'end) as maxt,max(case when rn2=1 then mint else '1900-01-01' end) as maxt from cte
group by senid
;WITH CTE AS(
SELECT senid,minv,maxv,maxt,mint
,CONVERT(VARCHAR(7),mint,120)minM
,CONVERT(VARCHAR(7),maxt,120)maxM
,ROW_NUMBER()OVER(PARTITION BY senid,CONVERT(VARCHAR(7),mint,120) ORDER BY minv)minRN
,ROW_NUMBER()OVER(PARTITION BY senid,CONVERT(VARCHAR(7),maxt,120) ORDER BY maxv DESC)maxRN
FROM TB
)
SELECT T1.senid,T1.minv,T1.mint,T2.maxv,T2.maxt
FROM CTE T1
JOIN CTE T2 ON T1.senid=T2.senid AND T1.minM=T2.maxM
WHERE T1.minRN=1 AND T2.maxRN=1
;with t AS
(
select senid, MAX(maxv) maxv, MIN(minv) minv
from tb
group by senid
)
select t.senid, t.maxv, MAX(tb.maxt) maxt, t.minv, MIN(t2.mint) mint
from t JOIN tb ON tb.senid=t.senid AND tb.maxv=t.maxv
JOIN tb t2 ON t2.senid=t.senid AND t2.minv=t.minv
group by t.senid, t.maxv, t.minv
如果最大值对应两个或以上的时间,取最大时间吗?
最小值也有同样问题
;with t AS
(
select senid, MAX(maxv) maxv, MIN(minv) minv
from tb
group by senid
)
select t.senid, t.maxv, tb.maxt, t.minv, t2.mint
from t JOIN tb ON tb.senid=t.senid AND tb.maxv=t.maxv
JOIN tb t2 ON t2.senid=t.senid AND t2.minv=t.minv
;WITH CTE AS(
SELECT senid,minv,maxv,maxt,mint
,ROW_NUMBER()OVER(PARTITION BY senid ORDER BY minv)minRN
,ROW_NUMBER()OVER(PARTITION BY senid ORDER BY maxv DESC)maxRN
FROM TB
)
SELECT T1.senid,T1.minv,T1.mint,T2.maxv,T2.maxt
FROM CTE T1
JOIN CTE T2 ON T1.senid=T2.senid
WHERE T1.minRN=1 AND T2.maxRN=2