22,210
社区成员
发帖
与我相关
我的任务
分享
;with tt as (
select *
,datediff(minute,'2000-1-1',dateadd(minute,分钟,dateadd(hour,小时,convert(datetime,观测日期))))/5 as tn
from #t -- 实际使用吧#t换成自己的表名
),ttt as (
select *,row_number() over(order by tn) as rid from tt a where not exists(select top 1 1 from tt where tn=a.tn-1)
),tttt as (
select * from ttt a
cross apply(
select count(0) as 最大观测次数 from tt where tn>=a.tn and tn<isnull((select tn from ttt where rid=a.rid+1),100000000)
) b
)
select b.*,(case when 最大观测次数=1 then 'A' when 最大观测次数>3 then 'C' else 'B' end) as tb
into #t1
from tttt a
cross apply (select * from tt where tn>=a.tn and tn<isnull((select tn from ttt where rid=a.rid+1),100000000)) b
select * from #t1
delete from 表A from #t1 a where a.tb in ('B','C') and a.id=表A.观测id
delete from 表B from #t1 a where a.tb in ('A','C') and a.id=表B.观测id
delete from 表C from #t1 a where a.tb in ('A','B') and a.id=表C.观测id
insert into 表A(观测id,观测日期,小时,分钟) select id,观测日期,小时,分钟 from #t1 a where tb='A' and not exists(select top 1 1 from 表A where 观测id=a.id)
insert into 表B(观测id,观测日期,小时,分钟) select id,观测日期,小时,分钟 from #t1 a where tb='B' and not exists(select top 1 1 from 表B where 观测id=a.id)
insert into 表C(观测id,观测日期,小时,分钟) select id,观测日期,小时,分钟 from #t1 a where tb='C' and not exists(select top 1 1 from 表C where 观测id=a.id)
drop table #t1
;with tt as (
select *
,datediff(minute,'2000-1-1',dateadd(minute,分钟,dateadd(hour,小时,convert(datetime,观测日期))))/5 as tn
from #t -- 实际使用吧#t换成自己的表名
),ttt as (
select *,row_number() over(order by tn) as rid from tt a where not exists(select top 1 1 from tt where tn=a.tn-1)
),tttt as (
select * from ttt a
cross apply(
select count(0) as 最大观测次数 from tt where tn>=a.tn and tn<isnull((select tn from ttt where rid=a.rid+1),100000000)
) b
)
select b.*,(case when 最大观测次数=1 then 'A' when 最大观测次数>3 then 'C' else 'B' end)
-- 可以在这个位置增加一个 into #t1 ,将结果放到临时表中,然后按照临时表的内容依次插入数据到不同的表中
from tttt a
cross apply (select * from tt where tn>=a.tn and tn<isnull((select tn from ttt where rid=a.rid+1),100000000)) b