我现在只想到了用游标的办法。
建一临时表用来存区段,再用游标循环票据号,再判断是否连接,否则精华贴里有一句查断号的讨论,建议去看看。
http://expert.csdn.net/Expert/topic/1021/1021698.xml?temp=.2430689
建一临时表用来存区段,再用游标循环票据号,再判断是否连接,否则精华贴里有一句查断号的讨论,建议去看看。
http://expert.csdn.net/Expert/topic/1021/1021698.xml?temp=.2430689
SELECT
CASE
WHEN std_score <60 THEN '60分以下:'
WHEN std_score >=60 and std_score <70 THEN '60-70:'
WHEN std_score >=70 and std_score <80 THEN '70-80:'
WHEN std_score >=80 and std_score <90 THEN '80-90:'
WHEN std_score >=90 and std_score <100 THEN '90-100:'
WHEN std_score =100 THEN '100:'
END AS "分数段",count(*) as "人数"
from std_score
group by
CASE
WHEN std_score <60 THEN '60分以下:'
WHEN std_score >=60 and std_score <70 THEN '60-70:'
WHEN std_score >=70 and std_score <80 THEN '70-80:'
WHEN std_score >=80 and std_score <90 THEN '80-90:'
WHEN std_score >=90 and std_score <100 THEN '90-100:'
WHEN std_score =100 THEN '100:'
END
declare @iid int
set @iid = 0
while exists (select * from a06 where fpno > @iid)
begin
select @iid = min(fpno) from a06 where fpno > @iid
insert #temp (s) values (@iid)
while exists (select * from a06 where fpno = @iid + 1)
set @iid = @iid + 1
update #temp set e = @iid where s = (select max(s) from #temp)
endselect cast(s as char(4))+'-'+cast(e as char(4)) from #temp
cid cname
1 cl0001
2 cl0002
4 cl0004
6 cl0006
7 cl0007
10 cl0010
12 cl0012
13 cl0013
14 cl0014
select * from
( select *,'+'+convert(varchar,(select min(cid) from clothid
where cid>a.cid)-(cid+1)) 'difference' from
clothid a where (select min(cid) from clothid where cid>
a.cid)-cid>=2
--此段程序检索不连续id的最小id号 检索结果为 id 2, 4, 7, 10
union all
select *,'-'+'' 'difference' from clothid a
where cid-(select max(cid) from clothid where cid<
a.cid)>=2) as outer_tab
--此段程序检索不连续id的最daid号 检索结果为 id 4,6,10,12
order by 1, case [difference] when '-' then 0 else [difference] end得到的结果是
cid cname difference
2 cl0002 +1
4 cl0004 -
4 cl0004 +1
6 cl0006 -
7 cl0007 +2
10 cl0010 -
10 cl0010 +1
12 cl0012 -
declare @a table (fpno int)
-----
insert @a select 1001
union all select 1002
union all select 1004
union all select 1007
union all select 1008
--用一句
select b.fpno beginno,
(select min(c.fpno) endno from @a c left join @a d on c.fpno+1=d.fpno where d.fpno is null and c.fpno>=b.fpno) endno
from @a a right join @a b on a.fpno+1=b.fpno where a.fpno is null
beginno endno
----------- -----------
1001 1002
1004 1004
1007 1008--用临时表,容易看明白
select identity(int,1,1) as id, b.fpno beginno into #t1 from @a a right join @a b on a.fpno+1=b.fpno where a.fpno is null
select identity(int,1,1) as id, a.fpno endno into #t2 from @a a left join @a b on a.fpno+1=b.fpno where b.fpno is null
select beginno,endno from #t1 a,#t2 b where a.id=b.id
drop table #t1
drop table #t2
beginno endno
----------- -----------
1001 1002
1004 1004
1007 1008
select b.fpno beginno,
(select min(c.fpno) endno from @a c left join @a d on c.fpno+1=d.fpno where d.fpno is null and c.fpno>=b.fpno) endno
from @a a right join @a b on a.fpno+1=b.fpno where a.fpno is null
高!