我需要分组查询13301,13302的最大值,最大值对应的时间,最小值,最小值对应的时间,这个改怎么写
13 个解决方案
#1
有没有人能帮帮忙,谢谢啦
#2
有没有人能帮帮忙,谢谢啦
#3
有没有人能帮帮忙,谢谢啦
#4
;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
#5
试试
;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
#6
楼上的方法用了,没有查出来
#7
楼主给生成测试数据的语句吧
#8
;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
如果最大值对应两个或以上的时间,取最大时间吗?
最小值也有同样问题
#9
如果最大值有多个时间,取其中一个
#10
如果按照senid还有时间按照月的分组,求最大值,最小值,最大值对应时间,最小值对应时间改怎么写
#11
;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
#12
;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
#13
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
#1
有没有人能帮帮忙,谢谢啦
#2
有没有人能帮帮忙,谢谢啦
#3
有没有人能帮帮忙,谢谢啦
#4
;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
#5
试试
;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
#6
楼上的方法用了,没有查出来
#7
楼主给生成测试数据的语句吧
#8
;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
如果最大值对应两个或以上的时间,取最大时间吗?
最小值也有同样问题
#9
如果最大值有多个时间,取其中一个
#10
如果按照senid还有时间按照月的分组,求最大值,最小值,最大值对应时间,最小值对应时间改怎么写
#11
;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
#12
;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
#13
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