update 表 set state='Y' WHERE ID>=400 AND ID<=600 update 表 set state='Y' WHERE ID>=800 AND ID<=900
SELECT * FROM 表 WHERE ID BETWEEN 1 AND 399 AND STATE='N' SELECT * FROM 表 WHERE ID BETWEEN 601 AND 799 AND STATE='N' SELECT * FROM 表 WHERE ID BETWEEN 901 AND 1000 AND STATE='N'
select top 1000 identity(int,1,1) id,'2004-10-10' createtime,'N' state into #t from sysobjects a,sysobjects b update #t set state='Y' where (id>=400 and id<=600) or (id>=800 and id<=900)select identity(int,1,1) sort,cast(id as int) id into #t1 from #t a where state='N' and not exists(select * from #t where state='N' and id=a.id-1)select identity(int,1,1) sort,cast(id as int) id into #t2 from #t a where state='N' and not exists(select * from #t where state='N' and id=a.id+1)select 未使用过区间=cast(#t1.id as varchar)+'-'+cast(#t2.id as varchar) from #t1,#t2 where #t1.sort=#t2.sortdrop table #t,#t1,#t2
Create proc pro_ddd as Create table #tmp(id int,crteatetime datetime,state varchar(1)) declare @i int set @i=1 while @i<=1000 Begin Insert iNTO #tmp Select @i,'2004-10-10','N' Set @i=@i+1 End Update #tmp Set state ='Y' Where id between 400 and 600 Select #tmp.*,identity(int,1,1)as Frow into #tmp2 From #tmp Where state='N'select identity(int,1,1) sort,cast(id as int) id into #t1 from #tmp a where state='N' and not exists(select * from #tmp where state='N' and id=a.id-1)select identity(int,1,1) sort,cast(id as int) id into #t2 from #tmp a where state='N' and not exists(select * from #tmp where state='N' and id=a.id+1)select 未使用过区间=cast(#t1.id as varchar)+'-'+cast(#t2.id as varchar) from #t1,#t2 where #t1.sort=#t2.sortdrop table #tmp,#t1,#t2
create table #t( id int, state char(1) )declare @i int set @i = 1 while(@i<1001) begin insert into #t select @i,'N' set @i =@i + 1 endupdate #t set state = 'Y' where id between 400 and 600 update #t set state = 'Y' where id between 800 and 900 select identity(int,1,1) as nid,a.id into #t1 from #t a where not exists(select 1 from #t where id = a.id-1 and state = 'N') and a.state = 'N'select identity(int,1,1) as nid,a.id into #t2 from #t a where not exists(select 1 from #t where id = a.id+1 and state = 'N') and a.state = 'N' select cast(a.id as varchar(10)) +'--' +cast(b.id as varchar(10)) as 未使用过区间 from #t1 a inner join #t2 b on a.nid = b.nid drop table #t drop table #t1 drop table #t2
select sum(case when ([id]>=1 and [id]<=399) then 1 else 0 end) as '[1--399]', sum(case when ([id]>=601 and [id]<=799) then 1 else 0 end) as '[601--799]' ...... from a where state='N'
create table #t( id int, state char(1) )declare @i int set @i = 1 while(@i<1001) begin insert into #t select @i,'N' set @i =@i + 1 endupdate #t set state = 'Y' where id between 400 and 600 update #t set state = 'Y' where id between 800 and 900select a.id aid,b.id bid into #t1 from #t a full join #t b on a.id=b.id+1 and a.state='N' and b.state='N' where a.id is null and b.state='N' or b.id is null and a.state='N' order by isnull(a.id,b.id) select identity(int,1,1) nid,* into #t2 from #t1 order by isnull(aid,bid)select cast(a.aid as varchar(10))+'-----'+cast(b.bid as varchar(10)) [未使用过区间] from #t2 a join #t2 b on a.nid=b.nid-1 and b.nid%2=0drop table #t drop table #t1 drop table #t2------------------------- 1-----399 601-----799 901-----1000
谢谢各位的回答! 我忘了写一点就是开始使用某段区间是会把字段createtime的时间更改为用的时候的系统时间。 例如:使用400-600时把createtime修改成2005-01-22,使用800-900时把createtime修改成 2005-01-25。 以上包括1000条记录的生成,使用400-600,800-900时的修改都不用劳驾各位去写语句的。 想请教最后这段: =================================== 未使用过区间 1--399 601--799 901--1000 =================================== 的sql语句!谢谢! 还有就是这(id>=400 and id<=600) or (id>=800 and id<=900),不能直接写的,是事前可能不知道的,也就是说要用sql语句求出的! 查询已用的区间的sql语句我是这样写的: select min(id)+'--'+max(id) as idarea,create from #t where state='Y' group by createtime
看来你还没有看明白,下面三句是你要的(#t代表你的表): select identity(int,1,1) sort,cast(id as int) id into #t1 from #t a where state='N' and not exists(select * from #t where state='N' and id=a.id-1)select identity(int,1,1) sort,cast(id as int) id into #t2 from #t a where state='N' and not exists(select * from #t where state='N' and id=a.id+1)select 未使用过区间=cast(#t1.id as varchar)+'-'+cast(#t2.id as varchar) from #t1,#t2 where #t1.sort=#t2.sort
if exists(select 1 from sysobjects where id=object_id('user_login') and objectproperty(id,'isTable')=1) drop table user_login gocreate table user_login(id int not null identity(1,1) ,crteatetime datetime not null default(getdate()),state varchar(1) not null,primary key(id)) goinsert into user_login(state) select top 400 'N' from sysobjects a,sysobjects b goinsert into user_login(state) select top 600 'N' from sysobjects a,sysobjects b goselect * from user_login /* id crteatetime state 1 2005-01-26 09:25:12.340 N 2 2005-01-26 09:25:12.340 N 3 2005-01-26 09:25:12.340 N 4 2005-01-26 09:25:12.340 N ..... 997 2005-01-26 09:25:12.390 N 998 2005-01-26 09:25:12.390 N 999 2005-01-26 09:25:12.390 N 1000 2005-01-26 09:25:12.390 N */--第一次更新 update user_login set state='Y' where id>=400 and id<=600 go--第二次更新 update user_login set state='Y' where id>=800 and id<=900 goif exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp1')) drop table #temp1if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp2')) drop table #temp2 gocreate table #temp1(www int not null identity(1,1),id int) create table #temp2(www int not null identity(1,1),id int) goinsert into #temp1 select id from user_login a where state='N' and id not in(select id+1 from user_login where state='N')insert into #temp2 select id from user_login a where state='N' and id not in(select id-1 from user_login where state='N')goselect cast(a.id as varchar(5))+'-'+cast(b.id as varchar(5)) 未使用區間 from #temp1 a join #temp2 b on a.www=b.www /* 未使用區間 1-399 601-799 901-1000 */
update 表 set state='Y' WHERE ID>=800 AND ID<=900
SELECT * FROM 表 WHERE ID BETWEEN 601 AND 799 AND STATE='N'
SELECT * FROM 表 WHERE ID BETWEEN 901 AND 1000 AND STATE='N'
into #t from sysobjects a,sysobjects b
update #t set state='Y' where (id>=400 and id<=600) or (id>=800 and id<=900)select identity(int,1,1) sort,cast(id as int) id into #t1 from #t a
where state='N' and not exists(select * from #t where state='N' and id=a.id-1)select identity(int,1,1) sort,cast(id as int) id into #t2 from #t a
where state='N' and not exists(select * from #t where state='N' and id=a.id+1)select 未使用过区间=cast(#t1.id as varchar)+'-'+cast(#t2.id as varchar)
from #t1,#t2 where #t1.sort=#t2.sortdrop table #t,#t1,#t2
as
Create table #tmp(id int,crteatetime datetime,state varchar(1))
declare @i int
set @i=1
while @i<=1000
Begin
Insert iNTO #tmp
Select @i,'2004-10-10','N'
Set @i=@i+1
End
Update #tmp Set state ='Y' Where id between 400 and 600
Select #tmp.*,identity(int,1,1)as Frow into #tmp2 From #tmp Where state='N'select identity(int,1,1) sort,cast(id as int) id into #t1 from #tmp a
where state='N' and not exists(select * from #tmp where state='N' and id=a.id-1)select identity(int,1,1) sort,cast(id as int) id into #t2 from #tmp a
where state='N' and not exists(select * from #tmp where state='N' and id=a.id+1)select 未使用过区间=cast(#t1.id as varchar)+'-'+cast(#t2.id as varchar)
from #t1,#t2 where #t1.sort=#t2.sortdrop table #tmp,#t1,#t2
id int,
state char(1)
)declare @i int
set @i = 1
while(@i<1001)
begin
insert into #t select @i,'N'
set @i =@i + 1
endupdate #t set state = 'Y' where id between 400 and 600
update #t set state = 'Y' where id between 800 and 900
select identity(int,1,1) as nid,a.id
into #t1
from #t a
where not exists(select 1 from #t where id = a.id-1 and state = 'N') and a.state = 'N'select identity(int,1,1) as nid,a.id
into #t2
from #t a
where not exists(select 1 from #t where id = a.id+1 and state = 'N') and a.state = 'N'
select cast(a.id as varchar(10)) +'--' +cast(b.id as varchar(10)) as 未使用过区间 from #t1 a inner join #t2 b on a.nid = b.nid
drop table #t
drop table #t1
drop table #t2
sum(case when ([id]>=1 and [id]<=399) then 1 else 0 end) as '[1--399]',
sum(case when ([id]>=601 and [id]<=799) then 1 else 0 end) as '[601--799]'
......
from a where state='N'
id int,
state char(1)
)declare @i int
set @i = 1
while(@i<1001)
begin
insert into #t select @i,'N'
set @i =@i + 1
endupdate #t set state = 'Y' where id between 400 and 600
update #t set state = 'Y' where id between 800 and 900select a.id aid,b.id bid into #t1 from #t a full join #t b
on a.id=b.id+1 and a.state='N' and b.state='N'
where a.id is null and b.state='N' or b.id is null and a.state='N'
order by isnull(a.id,b.id)
select identity(int,1,1) nid,* into #t2 from #t1 order by isnull(aid,bid)select cast(a.aid as varchar(10))+'-----'+cast(b.bid as varchar(10)) [未使用过区间]
from #t2 a join #t2 b on a.nid=b.nid-1 and b.nid%2=0drop table #t
drop table #t1
drop table #t2-------------------------
1-----399
601-----799
901-----1000
我忘了写一点就是开始使用某段区间是会把字段createtime的时间更改为用的时候的系统时间。
例如:使用400-600时把createtime修改成2005-01-22,使用800-900时把createtime修改成
2005-01-25。
以上包括1000条记录的生成,使用400-600,800-900时的修改都不用劳驾各位去写语句的。
想请教最后这段:
===================================
未使用过区间
1--399
601--799
901--1000
===================================
的sql语句!谢谢!
还有就是这(id>=400 and id<=600) or (id>=800 and id<=900),不能直接写的,是事前可能不知道的,也就是说要用sql语句求出的!
查询已用的区间的sql语句我是这样写的:
select min(id)+'--'+max(id) as idarea,create from #t where state='Y' group by createtime
select identity(int,1,1) sort,cast(id as int) id into #t1 from #t a
where state='N' and not exists(select * from #t where state='N' and id=a.id-1)select identity(int,1,1) sort,cast(id as int) id into #t2 from #t a
where state='N' and not exists(select * from #t where state='N' and id=a.id+1)select 未使用过区间=cast(#t1.id as varchar)+'-'+cast(#t2.id as varchar)
from #t1,#t2 where #t1.sort=#t2.sort
drop table user_login
gocreate table user_login(id int not null identity(1,1) ,crteatetime datetime not null default(getdate()),state varchar(1) not null,primary key(id))
goinsert into user_login(state)
select top 400 'N' from sysobjects a,sysobjects b
goinsert into user_login(state)
select top 600 'N' from sysobjects a,sysobjects b
goselect * from user_login
/*
id crteatetime state
1 2005-01-26 09:25:12.340 N
2 2005-01-26 09:25:12.340 N
3 2005-01-26 09:25:12.340 N
4 2005-01-26 09:25:12.340 N
.....
997 2005-01-26 09:25:12.390 N
998 2005-01-26 09:25:12.390 N
999 2005-01-26 09:25:12.390 N
1000 2005-01-26 09:25:12.390 N
*/--第一次更新
update user_login set state='Y' where id>=400 and id<=600
go--第二次更新
update user_login set state='Y' where id>=800 and id<=900
goif exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp1'))
drop table #temp1if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#temp2'))
drop table #temp2
gocreate table #temp1(www int not null identity(1,1),id int)
create table #temp2(www int not null identity(1,1),id int)
goinsert into #temp1
select id from user_login a where state='N' and id not in(select id+1 from user_login where state='N')insert into #temp2
select id from user_login a where state='N' and id not in(select id-1 from user_login where state='N')goselect cast(a.id as varchar(5))+'-'+cast(b.id as varchar(5)) 未使用區間 from #temp1 a join #temp2 b on a.www=b.www
/*
未使用區間
1-399
601-799
901-1000
*/