sql server 合并重复的相邻记录

Question

字段以及数据是：（表1）
   Devid    Systime        state
   1001      10:10:10        1                  t1   （即第一条state=1的记录）
   1001      10:10:20        1
   1001      10:10:28        1
   1001      10:10:40        1
   1001      10:11:05        0                  t2   （即第一条state=0的记录）
   1001      10:11:20        0
   1001      10:11:30        0
   1001      10:11:40        1                  t3   （即state=0之后第一条state=1的记录）
   1001      10:11:52        1
   1001      10:12:10        1
   1001      10:12:21        0                  t4   （即state=1之后第一条state=0的记录）
   1001      10:12:30        0
   1001      10:12:50        1                  t5   （同上）
   1001      10:13:11        0                  t6   （同上）   1002      10:10:11        1                  t1'   （同上）
   1002      10:10:23        1
   1002      10:10:40        1
   1002      10:11:05        0                  t2'
   1002      10:11:20        0
   1002      10:11:30        0
   1002      10:11:40        1                  t3'
   1002      10:11:52        1
   1002      10:12:10        1
   1002      10:12:21        0                  t4'
   1002      10:12:30        0
表2Devid    Systime        state
1001      10:10:10        1                  t1   （即第一条state=1的记录）
1001      10:11:05        0                  t2   （即第一条state=0的记录）
1001      10:11:40        1                  t3   （即state=0之后第一条state=1的记录）
1001      10:12:21        0                  t4   （即state=1之后第一条state=0的记录）
1001      10:12:50        1                  t5   （同上）
1001      10:13:11        0                  t6   （同上）
1002      10:10:11        1                  t1'   （同上）
1002      10:11:05        0                  t2'
1002      10:11:40        1                  t3'
1002      10:12:21        0                  t4'如何从表1变为表2

Answer 1

select * from 表名 a where not exists(select 1 from 表名 where devid=a.devid and state=a.state and systime<a.systime)
 

Answer 2

是通过select还是要通过delete获得？

Answer 3

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Devid] int,[Systime] datetime,[state] int)
insert [tb]
select 1001,'10:10:10',1 union all
select 1001,'10:10:20',1 union all
select 1001,'10:10:28',1 union all
select 1001,'10:10:40',1 union all
select 1001,'10:11:05',0 union all
select 1001,'10:11:20',0 union all
select 1001,'10:11:30',0 union all
select 1001,'10:11:40',1 union all
select 1001,'10:11:52',1 union all
select 1001,'10:12:10',1 union all
select 1001,'10:12:21',0 union all
select 1001,'10:12:30',0 union all
select 1001,'10:12:50',1 union all
select 1001,'10:13:11',0 union all
select 1002,'10:10:11',1 union all
select 1002,'10:10:23',1 union all
select 1002,'10:10:40',1 union all
select 1002,'10:11:05',0 union all
select 1002,'10:11:20',0 union all
select 1002,'10:11:30',0 union all
select 1002,'10:11:40',1 union all
select 1002,'10:11:52',1 union all
select 1002,'10:12:10',1 union all
select 1002,'10:12:21',0 union all
select 1002,'10:12:30',0
 
---查询---
select 
  Devid,
  Systime,
  state
from
  (select px=(select count(1)+1 from tb where Devid=t.Devid and systime<t.systime),* from tb t) a
where
  not exists(select 1 from (select px=(select count(1)+1 from tb where Devid=t.Devid and systime<t.systime),* from tb t) b where devid=a.devid and state=a.state and px=a.px-1)---结果---
Devid       Systime                                                state       
----------- ------------------------------------------------------ ----------- 
1001        1900-01-01 10:10:10.000                                1
1001        1900-01-01 10:11:05.000                                0
1001        1900-01-01 10:11:40.000                                1
1001        1900-01-01 10:12:21.000                                0
1001        1900-01-01 10:12:50.000                                1
1001        1900-01-01 10:13:11.000                                0
1002        1900-01-01 10:10:11.000                                1
1002        1900-01-01 10:11:05.000                                0
1002        1900-01-01 10:11:40.000                                1
1002        1900-01-01 10:12:21.000                                0（所影响的行数为 10 行）

Answer 4

select Devid,Systime,state 
  from
    (select *,row_number() over(partition by Devid,state order by Devid,Systime) as num from tb) a
  where num=1

Answer 5

declare @t table(Devid varchar(10),Systime varchar(10),state int)
insert into @t select 1001,'10:10:10',1
union all      select 1001,'10:10:20',1
union all      select 1001,'10:10:28',1
union all      select 1001,'10:10:40',1
union all      select 1001,'10:11:05',0
union all      select 1001,'10:11:20',0
union all      select 1001,'10:11:30',0
union all      select 1001,'10:11:40',1
union all      select 1001,'10:11:52',1
union all      select 1001,'10:12:10',1
union all      select 1001,'10:12:21',0
union all      select 1001,'10:12:30',0
union all      select 1001,'10:12:50',1
union all      select 1001,'10:13:11',0
union all      select 1002,'10:10:11',1
union all      select 1002,'10:10:23',1
union all      select 1002,'10:10:40',1
union all      select 1002,'10:11:05',0
union all      select 1002,'10:11:20',0
union all      select 1002,'10:11:30',0
union all      select 1002,'10:11:40',1
union all      select 1002,'10:11:52',1
union all      select 1002,'10:12:10',1
union all      select 1002,'10:12:21',0
union all      select 1002,'10:12:30',0
  
select
    t.*
from
    @t t
where
    t.state<>isnull((select top 1 state from @t where Devid=t.Devid and Systime<t.Systime order by Systime desc),1-t.state)/*
Devid      Systime    state       
---------- ---------- ----------- 
1001       10:10:10   1
1001       10:11:05   0
1001       10:11:40   1
1001       10:12:21   0
1001       10:12:50   1
1001       10:13:11   0
1002       10:10:11   1
1002       10:11:05   0
1002       10:11:40   1
1002       10:12:21   0
*/

Answer 6

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Devid] int,[Systime] datetime,[state] int)
insert [tb]
select 1001,'10:10:10',1 union all
select 1001,'10:10:20',1 union all
select 1001,'10:10:28',1 union all
select 1001,'10:10:40',1 union all
select 1001,'10:11:05',0 union all
select 1001,'10:11:20',0 union all
select 1001,'10:11:30',0 union all
select 1001,'10:11:40',1 union all
select 1001,'10:11:52',1 union all
select 1001,'10:12:10',1 union all
select 1001,'10:12:21',0 union all
select 1001,'10:12:30',0 union all
select 1001,'10:12:50',1 union all
select 1001,'10:13:11',0 union all
select 1002,'10:10:11',1 union all
select 1002,'10:10:23',1 union all
select 1002,'10:10:40',1 union all
select 1002,'10:11:05',0 union all
select 1002,'10:11:20',0 union all
select 1002,'10:11:30',0 union all
select 1002,'10:11:40',1 union all
select 1002,'10:11:52',1 union all
select 1002,'10:12:10',1 union all
select 1002,'10:12:21',0 union all
select 1002,'10:12:30',0
 alter table tb  add id int identity(1,1)select [Devid]  ,[Systime]  ,[state] 
from tb t 
where not  exists (select * from tb where id=t.id-1 and state=t.state)
/*
Devid       Systime                 state
----------- ----------------------- -----------
1001        1900-01-01 10:10:10.000 1
1001        1900-01-01 10:11:05.000 0
1001        1900-01-01 10:11:40.000 1
1001        1900-01-01 10:12:21.000 0
1001        1900-01-01 10:12:50.000 1
1001        1900-01-01 10:13:11.000 0
1002        1900-01-01 10:10:11.000 1
1002        1900-01-01 10:11:05.000 0
1002        1900-01-01 10:11:40.000 1
1002        1900-01-01 10:12:21.000 0(10 行受影响)
*/

Answer 7

create table kop1 (Devid varchar(10),Systime varchar(10),state int)
insert into kop1 select 1001,'10:10:10',1
union all      select 1001,'10:10:20',1
union all      select 1001,'10:10:28',1
union all      select 1001,'10:10:40',1
union all      select 1001,'10:11:05',0
union all      select 1001,'10:11:20',0
union all      select 1001,'10:11:30',0
union all      select 1001,'10:11:40',1
union all      select 1001,'10:11:52',1
union all      select 1001,'10:12:10',1
union all      select 1001,'10:12:21',0
union all      select 1001,'10:12:30',0
union all      select 1001,'10:12:50',1
union all      select 1001,'10:13:11',0
union all      select 1002,'10:10:11',1
union all      select 1002,'10:10:23',1
union all      select 1002,'10:10:40',1
union all      select 1002,'10:11:05',0
union all      select 1002,'10:11:20',0
union all      select 1002,'10:11:30',0
union all      select 1002,'10:11:40',1
union all      select 1002,'10:11:52',1
union all      select 1002,'10:12:10',1
union all      select 1002,'10:12:21',0
union all      select 1002,'10:12:30',0
  
select
  *,identity(int,1,1) as id 
into #lpo
from kop1 
order by Devid,Systime 
select Devid , Systime,state  
from #lpo t
where Id=1
union all
select Devid , Systime,state  
from #lpo t
where exists(select * from #lpo where t.state<>state and t.id=ID+1)/*
Devid      Systime    state
---------- ---------- -----------
1001       10:10:10   1
1001       10:11:05   0
1001       10:11:40   1
1001       10:12:21   0
1001       10:12:50   1
1001       10:13:11   0
1002       10:10:11   1
1002       10:11:05   0
1002       10:11:40   1
1002       10:12:21   0*/