| _覓 | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁 |
|
FrontPage › 3一豪Μ
|
|
|
螳 id螳 螳 磯殊 3覯 一朱 豪Μ 螳 觸螻 蟆曙磯. 讌レ 覦磯 覓殊企 襯 襷れ企瓦. れ 螻 蟾襾豪.
[edit]
1 覦覯1 #
drop table #temp
create table #temp(id varchar(20), win bit, dt datetime default getdate())
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('a', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
--insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 0) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1) waitfor delay '00:00:01'
insert #temp(id, win) values ('b', 1)
;with temp
as
(
select * , row_number() over(partition by id order by dt) rownum
from #temp
), rs1
as
(
select --*
a.id
, a.rownum
, max(b.rownum) max_rownum
, max(b.dt) max_dt
, isnull(count(case when b.win = 1 and a.rownum-b.rownum <> 1 then 1 end),0) cnt
, isnull(min(case when a.rownum-b.rownum = 1 then convert(int,b.win) end),0) prev1_row
from temp a inner join temp b
on a.id = b.id
and a.rownum in (b.rownum+1, b.rownum, b.rownum-1, b.rownum-2)
group by
a.id
, a.rownum
having isnull(count(case when b.win = 1 and a.rownum-b.rownum <> 1 then 1 end),0) = 3
), rs2
as
(
select *, row_number() over(partition by id order by rownum) rownum2
from rs1
)
select *
from rs2
where prev1_row = 0 or rownum2 % 3 = 1
order by id, rownum
鏤
|
磯ゴ 覯 讌 覈詩 譬 讌螳 . (襴ろ) |