一个表中有两列 S,T S表示状态比如:a,b,c,d...。T表示对应状态出现的时间,有没有办法用sql找出每个状态的起止时间?状态可能间隔重复出现。
比如:
S T
-------
a 1
a 2
b 3
b 4
b 5
a 6
a 7
c 8
能不能找出这样的结果啊?
a 1 2
b 3 5
a 6 7
c 8 8
比如:
S T
-------
a 1
a 2
b 3
b 4
b 5
a 6
a 7
c 8
能不能找出这样的结果啊?
a 1 2
b 3 5
a 6 7
c 8 8
insert @tb
select 'a', 1
union all select 'a', 2
union all select 'b',3
union all select 'b',4
union all select 'b', 5
union all select 'a', 6
union all select 'a', 7
union all select 'c', 8select s,MIN(t) t1,MAX(t) t2
from @tb
group by s--结果:
s t1 t2
a 1 7
b 3 5
c 8 8
结果能不能有 a 1 2, a 6 7 两个,不能是a 1 7
没有奇偶之分,中间间隔无所谓。
我就是想找到按时间S的顺序和起止。就是(t1,t2)之间S都是一样的就行
就是按T的先后两个a之间可能还有b,c...不能笼统的找一个最大和最小,需要分成两段
create table tb(s varchar(10),t int)
insert tb
select 'a', 1 union all
select 'a', 2 union all
select 'b', 3 union all
select 'b', 4 union all
select 'b', 5 union all
select 'a', 6 union all
select 'a', 7 union all
select 'c', 8
with cte1 as
(
select row_number() over(order by a.T) as rowid,a.S,a.T from tb a
left join tb b on a.T+1 = b.T
where a.S <> isnull(b.S,'')
),
cte2 as
(
select row_number() over(order by a.T) as rowid,a.S,a.T from tb a
left join tb b on a.T-1 = b.T
where a.S <> isnull(b.S,'')
)
select a.S, b.T, a.T from cte1 a join cte2 b on a.rowid = b.rowid/*
S T T
---------- ----------- -----------
a 1 2
b 3 5
a 6 7
c 8 8(4 行受影响)
*/
insert #tb
select 'a', 1
union all select 'a', 2
union all select 'b',3
union all select 'b',4
union all select 'b', 5
union all select 'a', 6
union all select 'a', 7
union all select 'c', 8;with cte as
(select id=(select count(1)+1 from #tb t where t.t<#tb.t),rn=(select count(1)+1 from #tb t where t.s=#tb.s and t.t<#tb.t),s,t from #tb
)
,t as
(select hh=id-rn,s,min(t) minT,max(t) maxT from cte group by id-rn,s)
select s,mint,maxt from t/**
s mint maxt
---------- ----------- -----------
a 1 2
a 6 7
b 3 5
c 8 8(4 行受影响)
**/
谢谢你的回答,T不是连续的,Row_Number()派生一个列出来怎么弄啊,可以说得详细点么?我不懂,谢谢啦
(
select row_number() over(order by T) as rowid,S,T from tb
),
cte2 as
(
select row_number() over(order by a.rowid) as rowid,a.S,a.T from cte1 a
left join cte1 b on a.rowid+1 = b.rowid
where a.S <> isnull(b.S,'')
),
cte3 as
(
select row_number() over(order by a.rowid) as rowid,a.S,a.T from cte1 a
left join cte1 b on a.rowid-1 = b.rowid
where a.S <> isnull(b.S,'')
)
select a.S, b.T, a.T from cte2 a join cte3 b on a.rowid = b.rowid
PS:#11也完全可以满足你的要求。
我分别调试了一下,以下语句效率最高。Select a.S, Min(T) AS MinT, Max(T) AS MaxT From
(
Select
id=Row_Number() Over(Order By T),
rn=Row_Number() Over(Partition By S Order By T),S,T
From tb
) a
Group By a.S,a.id-a.rn/*
S MinT MaxT
---------- ----------- -----------
a 1 2
b 3 5
a 6 7
c 8 8(4 行受影响)
*/