
我需要分组查询13301,13302的最大值,最大值对应的时间,最小值,最小值对应的时间,这个改怎么写
------解决思路----------------------
;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
------解决思路----------------------
试试
;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
,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 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
------解决思路----------------------
try this
;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