导入到SQL中再处理 :) declare @tb table (A int, B nvarchar(2)) insert @tb select 1, null union all select 2, 'A' union all select 2, 'A' union all select 2, null union all select 2, 'B' union all select 2, null union all select 2, null union all select 2, 'C' union all select 2, 'C' select id=identity(int,1,1),* into # from @tbupdate a set A = (select count(distinct B) from # where B is not null and id <=a.id) from # aselect * from # /* id A B ----------- ----------- ---- 1 0 NULL 2 1 A 3 1 A 4 1 NULL 5 2 B 6 2 NULL 7 2 NULL 8 3 C 9 3 C */
drop table # go declare @tb table (A int, B nvarchar(2)) insert @tb select 1, null union all select 2, 'A' union all select 2, 'A' union all select 2, null union all select 2, 'B' union all select 2, null union all select 2, null union all select 2, 'C' union all select 2, 'C' select id=identity(int,1,1),* into # from @tbupdate a set A = (select count(B) from # where B is not null and id <=a.id) from # aselect * from # /* id A B ----------- ----------- ---- 1 0 NULL 2 1 A 3 2 A 4 2 NULL 5 3 B 6 3 NULL 7 3 NULL 8 4 C 9 5 C */
create table #a(a int, b varchar(10)) insert #a(b) select null insert #a(b) select 'a' insert #a(b) select 'c' insert #a(b) select 'd' insert #a(b) select null insert #a(b) select 'b' insert #a(b) select 'f' insert #a(b) select 'e' ------------------truncate table #a declare @a int set @a=0 update #a set @a=@a+(case when b is null then 0 else 1 end),a=@a select * from #a --------------------- 0 NULL 1 a 2 c 3 d 3 NULL 4 b 5 f 6 e
select id=identity(int,1,1),* into # from tmp_ceshiguanliupdate a set 测试编号 = (select count(distinct 检测项目,检测方法) from # where 检测项目 is not null and id <=a.id) +5637 from # aselect * from #如上,我想以“检测项目和检测方法”同时发生变化后为标准,然后测试编号+1,如何实现? 以上代码执行报错:服务器: 消息 170,级别 15,状态 1,行 1 第 1 行: ',' 附近有语法错误。
declare @tb table (A int, B nvarchar(2))
insert @tb
select 1, null union all
select 2, 'A' union all
select 2, 'A' union all
select 2, null union all
select 2, 'B' union all
select 2, null union all
select 2, null union all
select 2, 'C' union all
select 2, 'C' select id=identity(int,1,1),* into # from @tbupdate a
set A = (select count(distinct B) from # where B is not null and id <=a.id)
from # aselect * from #
/*
id A B
----------- ----------- ----
1 0 NULL
2 1 A
3 1 A
4 1 NULL
5 2 B
6 2 NULL
7 2 NULL
8 3 C
9 3 C
*/
go
declare @tb table (A int, B nvarchar(2))
insert @tb
select 1, null union all
select 2, 'A' union all
select 2, 'A' union all
select 2, null union all
select 2, 'B' union all
select 2, null union all
select 2, null union all
select 2, 'C' union all
select 2, 'C' select id=identity(int,1,1),* into # from @tbupdate a
set A = (select count(B) from # where B is not null and id <=a.id)
from # aselect * from #
/*
id A B
----------- ----------- ----
1 0 NULL
2 1 A
3 2 A
4 2 NULL
5 3 B
6 3 NULL
7 3 NULL
8 4 C
9 5 C
*/
insert #a(b) select null
insert #a(b) select 'a'
insert #a(b) select 'c'
insert #a(b) select 'd'
insert #a(b) select null
insert #a(b) select 'b'
insert #a(b) select 'f'
insert #a(b) select 'e'
------------------truncate table #a
declare @a int
set @a=0
update #a set @a=@a+(case when b is null then 0 else 1 end),a=@a
select * from #a
---------------------
0 NULL
1 a
2 c
3 d
3 NULL
4 b
5 f
6 e
set 测试编号 = (select count(distinct 检测项目,检测方法) from # where 检测项目 is not null and id <=a.id) +5637
from # aselect * from #如上,我想以“检测项目和检测方法”同时发生变化后为标准,然后测试编号+1,如何实现?
以上代码执行报错:服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。