SQL数据表怎么截取连续票号，输出最小到最大排序，断号用"-"表示，查出效果如下张1：1001~1003，1009~10013；张2:1004~1008

[此贴子已经被作者于2016-6-22 10:36编辑过]

--条件,BH字段为int或者数字的字符,如下(BH=票号,XS=销售)
--create table #tmp(bh varchar(6),xs varchar(2))
--insert into #tmp(bh,xs) values('10001','否')
--insert into #tmp(bh,xs) values('10002','否')
--insert into #tmp(bh,xs) values('10003','否')
--insert into #tmp(bh,xs) values('10004','否')
--insert into #tmp(bh,xs) values('10005','否')
--insert into #tmp(bh,xs) values('10006','是')
--insert into #tmp(bh,xs) values('10007','否')
--insert into #tmp(bh,xs) values('10008','否')
--insert into #tmp(bh,xs) values('10009','否')

update #tmp set xs='是' where bh='10007'

select a.bh,a.xs,a.nx from (select bh,xs,cast(bh as int)+1 as nx from #tmp where xs='否') a,(select bh,xs from #tmp)b
where a.xs='否' and b.xs='否' and a.nx=b.bh
union all
select * from (select top 1 *,cast(bh as int)+1 as nx from #tmp order by bh desc)c where xs='否' --把最后1个统计进来
--还有点问题..

[此贴子已经被作者于2016-6-22 17:08编辑过]

--表结构(BH=票号,XS=销售,LP=联票)
--PBH为上1个票号,NPH为下1个票号,int
--SQL2000测试通过,计算出那些是联票
--create table #tmp(bh int,xs varchar(2))
--GO
--insert into #tmp(bh,xs) values(10001,'否')
--insert into #tmp(bh,xs) values(10002,'否')
--insert into #tmp(bh,xs) values(10003,'否')
--insert into #tmp(bh,xs) values(10004,'否')
--insert into #tmp(bh,xs) values(10005,'否')
--insert into #tmp(bh,xs) values(10006,'是')
--insert into #tmp(bh,xs) values(10007,'否')
--insert into #tmp(bh,xs) values(10008,'否')
--Insert into #tmp(bh,xs) values(10009,'否')
--Insert into #tmp(bh,xs) values(10010,'否')
--Insert into #tmp(bh,xs) values(10011,'否')

update #tmp set xs='否' where bh=10002 --改变某个BH的XS值,测试结果

select d.*,(case when xs='否' and(nxs='否' or pxs='否') then '联' else '否' end) as lp from
(select a.pbh,a.bh,a.xs,a.nbh,b.xs as nxs,c.xs as pxs from
 (select bh-1 as pbh,bh,xs,bh+1 as nbh from #tmp)a
 left join (select * from #tmp)b on a.nbh=b.bh
 left join (select * from #tmp)c on a.pbh=c.bh)d