我有这样一个表:表名为data 数据如下格式no_date name dep
1 a c
2 a c
3 a c
4 a c
6 a c
8 a c
9 a c
11 a c
12 a c
1 b c
2 b c
4 b c
5 b c
6 b c
7 b c
8 b c
11 b c
12 b c我想删除列no_date中数值不连续的记录,按列name来判断(例如姓名a的1,2,3,4为连续数值,6为不连续数值,删除6这一行的记录,按照列name分组a判断完了再判断b再往下判断。。),如何实现?请各位帮忙
1 a c
2 a c
3 a c
4 a c
6 a c
8 a c
9 a c
11 a c
12 a c
1 b c
2 b c
4 b c
5 b c
6 b c
7 b c
8 b c
11 b c
12 b c我想删除列no_date中数值不连续的记录,按列name来判断(例如姓名a的1,2,3,4为连续数值,6为不连续数值,删除6这一行的记录,按照列name分组a判断完了再判断b再往下判断。。),如何实现?请各位帮忙
where not exists(select 1 from data where name=t.name and no_date=t.no_date-1)
and t.no_date<>1
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (no_date int,name varchar(1),dep varchar(1))
insert into #tb
select 1,'a','c' union all
select 2,'a','c' union all
select 3,'a','c' union all
select 4,'a','c' union all
select 6,'a','c' union all
select 8,'a','c' union all
select 9,'a','c' union all
select 11,'a','c' union all
select 12,'a','c' union all
select 1,'b','c' union all
select 2,'b','c' union all
select 4,'b','c' union all
select 5,'b','c' union all
select 6,'b','c' union all
select 7,'b','c' union all
select 8,'b','c' union all
select 11,'b','c' union all
select 12,'b','c'alter table #tb add id intupdate #tb set id =no_date-(select count(*) from #tb where name=t.name and no_date<=t.no_date)
from #tb tdelete #tb
from #tb t where id in (select id from #tb where name=t.name group by id having(count(*)=1))select * from #tbno_date name dep id
----------- ---- ---- -----------
1 a c 0
2 a c 0
3 a c 0
4 a c 0
8 a c 2
9 a c 2
11 a c 3
12 a c 3
1 b c 0
2 b c 0
4 b c 1
5 b c 1
6 b c 1
7 b c 1
8 b c 1
11 b c 3
12 b c 3(17 行受影响)
from #tb t 运行出现“缺少函数名 )”的错误