select id, id+99 from 表a a
where cardNoBegin is null
and not exists(select 1 from 表a where cardNoBegin is null and id between a.id+1 and a.id+99)
where cardNoBegin is null
and not exists(select 1 from 表a where cardNoBegin is null and id between a.id+1 and a.id+99)
解决方案 »
- sql server 语言设置
- 制作安装文件时的问题
- 向各位老大求救一个关于返回总纪录数的有条件的存储过程
- 专用管理员DAC连接问题,我的数据库是sql2008 想用专用管理员(DAC)连接
- 老师让画个E-R图,始终没有搞清楚
- 初学sql的疑问
- SQL Server 2000为什么要两张盘??
- 如何将文本文件准确无误的导进SQL库?
- 表中有一个time字段,是datetime类型,现在要把Getdate()减去那个字段的时间后返回秒数,sql怎么写?
- 我想用delphi + asp + sql server 做个管理系统,大家有什么好的建议?参与就给分!
- 各位大虾帮小弟一把 存储过程问题~~
- 如何使用TSQL根据条件发邮件通知?
set @n=100
select StartID=id, EndID=id+@n-1 from 表a a
where cardNoBegin is null
and not exists(select 1 from 表a where cardNoBegin is null
and id between a.id+1 and a.id+@n-1)
--> 测试数据:[a]
if object_id('[a]') is not null drop table [a]
create table [a]([id] int,[cardNo] varchar(7),[cardNoBegin] varchar(7))
insert [a]
select 611,'0150501','aaaaaa' union all
select 612,'0150502','bbbbbb' union all
select 613,'0150503',null union all
select 614,'0150504','ccccccc' union all
select 615,'0150505',null union all
select 616,'0150506',null union all
select 617,'0150507','ssssss' union all
select 618,'0150508',null union all
select 619,'0150509',null union all
select 620,'0150510',nulldeclare @n int
set @n=3
print '@n='+rtrim(@n)
select StartID=id, EndID=id+@n-1 from a ta
where cardNoBegin is null
and not exists(select 1 from a where cardNoBegin is null
and id between ta.id+1 and ta.id+@n-1)
/*
@n=3
StartID EndID
----------- -----------
620 622(1 行受影响)
*/drop table a
SQL2005,ID不连续下取值。DECLARE @I INT;
SET @I = 3;WITH DATA AS
(
SELECT [id],[cardNo],[cardNoBegin],FLAG = CASE WHEN [cardNoBegin] IS NULL THEN 0 ELSE 1 END
FROM [A]
),
NUM AS
(
SELECT [id],[cardNo],[cardNoBegin],FLAG,RN= ROW_NUMBER () OVER ( ORDER BY [id])
,RN2 = ROW_NUMBER () OVER ( PARTITION BY FLAG ORDER BY [id])
FROM DATA
)
SELECT A.CARDNO,B.CARDNO
FROM NUM A,NUM B
WHERE B.RN2 - A.RN2 = B.RN - A.RN
AND B.RN - A.RN + 1 =@I
AND A.FLAG = 0 AND B.FLAG = 0
ORDER BY A.RN;
insert #AA
select 611,'0150501','aaaaaa' union all
select 612,'0150502','bbbbbb' union all
select 613,'0150503',null union all
select 614,'0150504','ccccccc' union all
select 615,'0150505',null union all
select 616,'0150506',null union all
select 617,'0150507','ssssss' union all
select 618,'0150508',null union all
select 619,'0150509',null union all
select 620,'0150510',null
select top 5 * from #A where id in (select id from #A where [cardNoBegin] is null)
create table [#a]([id] int,[cardNo] varchar(7),[cardNoBegin] varchar(7))
insert [#a]
select 611,'0150501','aaaaaa' union all
select 612,'0150502','bbbbbb' union all
select 613,'0150503',null union all
select 614,'0150504','ccccccc' union all
select 615,'0150505',null union all
select 616,'0150506',null union all
select 617,'0150507','ssssss' union all
select 618,'0150508',null union all
select 619,'0150509',null union all
select 620,'0150510',nulldeclare @n int
set @n=3
select id [begin],id+@n-1 [end] from #a a1 where (select count(*) from #a a2 where a2.id>=a1.id and a2.id<a1.id+@n and a2.cardNoBegin is null)=@n
这样?
if object_id('[a]') is not null drop table [a]
create table [a]([id] int,[cardNo] varchar(7),[cardNoBegin] varchar(100))
insert [a]
select 611,'0150501','aaaaaa' union all
select 612,'0150502','bbbbbb' union all
select 613,'0150503',null union all
select 614,'0150504','ccccccc' union all
select 615,'0150505',null union all
select 616,'0150506',null union all
select 617,'0150507','ssssss' union all
select 618,'0150508',null union all
select 619,'0150509',null union all
select 620,'0150510',nullselect 0 as CID,* into # from a
update # set cardnobegin ='cardnobegin' where cardnobegin is nulldeclare @c int,@name varchar(100),@CID int
set @CID=0
set @c=1
set @name='aaaaaa'
update # set @CID=case when @name=cardnobegin then @c else @c+1 end,
@c=case when @name=cardnobegin then @c else @c+1 end,
@name=cardnobegin,
CID=@cid declare @a int
set @a=3
if @a != 1
begin
select id,cardNo,null as cardnobegin from # where
cardnobegin ='cardnobegin' and CID=(
select CID from # group by CID having count(*)=@a)
endset @a=2
if @a != 1
begin
select id,cardNo,null as cardnobegin from # where
cardnobegin ='cardnobegin' and CID=(
select CID from # group by CID having count(*)=@a)
end
drop table #/*
id cardNo cardnobegin
----------- ------- -----------
618 0150508 NULL
619 0150509 NULL
620 0150510 NULL(3 行受影响)id cardNo cardnobegin
----------- ------- -----------
615 0150505 NULL
616 0150506 NULL(2 行受影响)
*/