如果是这样的话,需要使用游标去算.游标的基本写法declare @id int,@name varchar(20); declare cur cursor fast_forward for select id,name from a; open cur; fetch next from cur into @id,@name; while @@fetch_status=0 begin --做你要做的事 fetch next from cur into @id,@name; end close cur; deallocate cur;参照上面的写法自己写写看,如果需要帮写,请说明.
--sql 2005 create table A(code varchar(10),status int,ue varchar(10)) insert into A select '100001' ,0 ,'admin' union all select '100002' ,0 ,'admin' union all select '100003' ,1 ,'admin' union all select '100004' ,1 ,'admin' union all select '100005' ,0 ,'admin' union all select '100006' ,0 ,'admin' union all select '100007' ,0 ,'admin' union all select '100008' ,0 ,'admin' union all select '100009' ,0 ,'admin' union all select '1000010' ,1 ,'admin' union all select '1000011' ,1 ,'admin' union all select '1000012' ,1 ,'admin' union all select '1000013' ,1 ,'admin' go;with ach as ( select *,id=row_number() over (order by getdate()), rid=row_number() over (partition by ue,status order by getdate()) from A )select min(id) as [startcode],max(id) as [endcode],count(*) as cnt,status from ach group by ue,status,id-riddrop table A/******************startcode endcode cnt status -------------------- -------------------- ----------- ----------- 1 2 2 0 5 9 5 0 3 4 2 1 10 13 4 1(4 行受影响)
--sql 2005 create table A(code varchar(10),status int,ue varchar(10)) insert into A select '100001' ,0 ,'admin' union all select '100002' ,0 ,'admin' union all select '100003' ,1 ,'admin' union all select '100004' ,1 ,'admin' union all select '100005' ,0 ,'admin' union all select '100006' ,0 ,'admin' union all select '100007' ,0 ,'admin' union all select '100008' ,0 ,'admin' union all select '100009' ,0 ,'admin' union all select '1000010' ,1 ,'admin' union all select '1000011' ,1 ,'admin' union all select '1000012' ,1 ,'admin' union all select '1000013' ,1 ,'admin' goselect * into B from A go alter table B add id int identity(1,1) alter table B add flag int godeclare @id int declare @status int declare @ue varchar(10) set @id = 0update B set @id = (case when @ue = ue and @status = status then @id else @id + 1 end), @ue = ue, @status = status, flag = @idselect min(id) as startcode,max(id) as endcode,count(*) as cnt,status from B group by ue,status,flagdrop table A,B/*************************startcode endcode cnt status ----------- ----------- ----------- ----------- 1 2 2 0 5 9 5 0 3 4 2 1 10 13 4 1(4 行受影响)
create table tb1(col varchar(10),type int) insert into tb1 values('100001' ,0) insert into tb1 values('100002' ,0) insert into tb1 values('100003' ,1) insert into tb1 values('100004' ,1) insert into tb1 values('100005' ,0) insert into tb1 values('100006' ,0) insert into tb1 values('100007' ,0) insert into tb1 values('100008' ,0) insert into tb1 values('100009' ,0) insert into tb1 values('1000010' ,1) insert into tb1 values('1000011' ,1) insert into tb1 values('1000012' ,1) insert into tb1 values('1000013' ,1) create table tb2(开始条码号 varchar(10),结束条码号 varchar(10),数量 int,状态 varchar(10)) go declare @type1 int,@type2 int,@cnt int , @col1 varchar(10) , @col2 varchar(10) , @col3 varchar(10); set @type2 = -1 set @cnt = 0 set @col2 = '' set @col3 = '' declare cur cursor fast_forward for select type , col from tb1; open cur; fetch next from cur into @type1,@col1; while @@fetch_status=0 begin if @type2 <> @type1 begin if @cnt >= 1 begin insert into tb2 values(@col3 , @col2 , @cnt , case when @type2 = 0 then '未使用' else '已使用' end) end set @cnt = 1 set @col2 = @col1 set @col3 = @col1 set @type2 = @type1 end else begin set @col2 = @col1 set @cnt = @cnt + 1 end fetch next from cur into @type1 , @col1; end insert into tb2 values(@col3 , @col2 , @cnt , case when @type2 = 0 then '未使用' else '已使用' end)close cur; deallocate cur;select * from tb2drop table tb1 , tb2/* 开始条码号 结束条码号 数量 状态 ---------- ---------- ----------- ---------- 100001 100002 2 未使用 100003 100004 2 已使用 100005 100009 5 未使用 1000010 1000013 4 已使用(所影响的行数为 4 行) */
create table A(code varchar(10),status int,ue varchar(10)) insert into A select '100001' ,0 ,'admin' union all select '100002' ,0 ,'admin' union all select '100003' ,1 ,'admin' union all select '100004' ,1 ,'admin' union all select '100005' ,0 ,'admin' union all select '100006' ,0 ,'admin' union all select '100007' ,0 ,'admin' union all select '100008' ,0 ,'admin' union all select '100009' ,0 ,'admin' union all select '1000010' ,1 ,'admin' union all select '1000011' ,1 ,'admin' union all select '1000012' ,1 ,'admin' union all select '1000013' ,1 ,'admin' goselect * into B from A go alter table B add id int identity(1,1) alter table B add flag int godeclare @id int declare @status int declare @ue varchar(10) set @id = 0update B set @id = (case when @ue = ue and @status = status then @id else @id + 1 end), @ue = ue, @status = status, flag = @idselect a.code as start,b.code as [end],c.cnt,c.status from B a,B b, (select min(id) as startcode,max(id) as endcode,count(*) as cnt,status from B group by ue,status,flag) c where a.id = c.startcode and b.id = c.endcodedrop table A,B/*****************start end cnt status ---------- ---------- ----------- ----------- 100001 100002 2 0 100003 100004 2 1 100005 100009 5 0 1000010 1000013 4 1(4 行受影响)
例如表的结构,表之间的关系,测试数据,相关算法及需要的结果。
这样有助于我们理解你的意思,更主要的是能尽快让你获得答案或解决问题的方法。
你想要什么样的结果?
100001 0 admin
100002 0 admin
100003 1 admin
100004 1 admin
100005 0 admin
100006 0 admin
100007 0 admin
100008 0 admin
100009 0 admin
1000010 1 admin
1000011 1 admin
1000012 1 admin
1000013 1 admin0代表未使用 1 代表已使用
declare cur cursor fast_forward for
select id,name from a;
open cur;
fetch next from cur into @id,@name;
while @@fetch_status=0
begin
--做你要做的事
fetch next from cur into @id,@name;
end
close cur;
deallocate cur;参照上面的写法自己写写看,如果需要帮写,请说明.
--sql 2005
create table A(code varchar(10),status int,ue varchar(10))
insert into A
select '100001' ,0 ,'admin' union all
select '100002' ,0 ,'admin' union all
select '100003' ,1 ,'admin' union all
select '100004' ,1 ,'admin' union all
select '100005' ,0 ,'admin' union all
select '100006' ,0 ,'admin' union all
select '100007' ,0 ,'admin' union all
select '100008' ,0 ,'admin' union all
select '100009' ,0 ,'admin' union all
select '1000010' ,1 ,'admin' union all
select '1000011' ,1 ,'admin' union all
select '1000012' ,1 ,'admin' union all
select '1000013' ,1 ,'admin'
go;with ach as
(
select *,id=row_number() over (order by getdate()),
rid=row_number() over (partition by ue,status order by getdate())
from A
)select min(id) as [startcode],max(id) as [endcode],count(*) as cnt,status
from ach
group by ue,status,id-riddrop table A/******************startcode endcode cnt status
-------------------- -------------------- ----------- -----------
1 2 2 0
5 9 5 0
3 4 2 1
10 13 4 1(4 行受影响)
--sql 2005
create table A(code varchar(10),status int,ue varchar(10))
insert into A
select '100001' ,0 ,'admin' union all
select '100002' ,0 ,'admin' union all
select '100003' ,1 ,'admin' union all
select '100004' ,1 ,'admin' union all
select '100005' ,0 ,'admin' union all
select '100006' ,0 ,'admin' union all
select '100007' ,0 ,'admin' union all
select '100008' ,0 ,'admin' union all
select '100009' ,0 ,'admin' union all
select '1000010' ,1 ,'admin' union all
select '1000011' ,1 ,'admin' union all
select '1000012' ,1 ,'admin' union all
select '1000013' ,1 ,'admin'
goselect * into B from A
go
alter table B add id int identity(1,1)
alter table B add flag int
godeclare @id int
declare @status int
declare @ue varchar(10)
set @id = 0update B
set @id = (case when @ue = ue and @status = status then @id else @id + 1 end),
@ue = ue,
@status = status,
flag = @idselect min(id) as startcode,max(id) as endcode,count(*) as cnt,status
from B
group by ue,status,flagdrop table A,B/*************************startcode endcode cnt status
----------- ----------- ----------- -----------
1 2 2 0
5 9 5 0
3 4 2 1
10 13 4 1(4 行受影响)
insert into tb1 values('100001' ,0)
insert into tb1 values('100002' ,0)
insert into tb1 values('100003' ,1)
insert into tb1 values('100004' ,1)
insert into tb1 values('100005' ,0)
insert into tb1 values('100006' ,0)
insert into tb1 values('100007' ,0)
insert into tb1 values('100008' ,0)
insert into tb1 values('100009' ,0)
insert into tb1 values('1000010' ,1)
insert into tb1 values('1000011' ,1)
insert into tb1 values('1000012' ,1)
insert into tb1 values('1000013' ,1)
create table tb2(开始条码号 varchar(10),结束条码号 varchar(10),数量 int,状态 varchar(10))
go
declare @type1 int,@type2 int,@cnt int , @col1 varchar(10) , @col2 varchar(10) , @col3 varchar(10);
set @type2 = -1
set @cnt = 0
set @col2 = ''
set @col3 = ''
declare cur cursor fast_forward for
select type , col from tb1;
open cur;
fetch next from cur into @type1,@col1;
while @@fetch_status=0
begin
if @type2 <> @type1
begin
if @cnt >= 1
begin
insert into tb2 values(@col3 , @col2 , @cnt , case when @type2 = 0 then '未使用' else '已使用' end)
end
set @cnt = 1
set @col2 = @col1
set @col3 = @col1
set @type2 = @type1
end
else
begin
set @col2 = @col1
set @cnt = @cnt + 1
end fetch next from cur into @type1 , @col1;
end
insert into tb2 values(@col3 , @col2 , @cnt , case when @type2 = 0 then '未使用' else '已使用' end)close cur;
deallocate cur;select * from tb2drop table tb1 , tb2/*
开始条码号 结束条码号 数量 状态
---------- ---------- ----------- ----------
100001 100002 2 未使用
100003 100004 2 已使用
100005 100009 5 未使用
1000010 1000013 4 已使用(所影响的行数为 4 行)
*/
create table A(code varchar(10),status int,ue varchar(10))
insert into A
select '100001' ,0 ,'admin' union all
select '100002' ,0 ,'admin' union all
select '100003' ,1 ,'admin' union all
select '100004' ,1 ,'admin' union all
select '100005' ,0 ,'admin' union all
select '100006' ,0 ,'admin' union all
select '100007' ,0 ,'admin' union all
select '100008' ,0 ,'admin' union all
select '100009' ,0 ,'admin' union all
select '1000010' ,1 ,'admin' union all
select '1000011' ,1 ,'admin' union all
select '1000012' ,1 ,'admin' union all
select '1000013' ,1 ,'admin'
goselect * into B from A
go
alter table B add id int identity(1,1)
alter table B add flag int
godeclare @id int
declare @status int
declare @ue varchar(10)
set @id = 0update B
set @id = (case when @ue = ue and @status = status then @id else @id + 1 end),
@ue = ue,
@status = status,
flag = @idselect a.code as start,b.code as [end],c.cnt,c.status
from B a,B b,
(select min(id) as startcode,max(id) as endcode,count(*) as cnt,status
from B
group by ue,status,flag) c
where a.id = c.startcode and b.id = c.endcodedrop table A,B/*****************start end cnt status
---------- ---------- ----------- -----------
100001 100002 2 0
100003 100004 2 1
100005 100009 5 0
1000010 1000013 4 1(4 行受影响)