不好意思,昨天没在,是字符型的,大家有什么好的方法不?我想到,只要能增加一列,是连续的号用一个值班来表示,比如 a 001 a 002 a 003b 005 就可以用分组取最小值,最大值来生成记录
老夫我不会啊,int型的倒是会呢,帮你顶顶
create table test(field varchar(03)) insert into test values('001') insert into test values('002') insert into test values('003') insert into test values('005') insert into test values('006') insert into test values('008') insert into test values('010') insert into test values('011') insert into test values('015') insert into test values('016') insert into test values('017')go ;with cte_test as ( select field, field as new_field from test as T where not exists(select 1 from test where convert(int,field) = convert(int,T.field)-1) union all select A.field,B.field from cte_test as A, test as B where convert(int,A.new_field)+1= convert(int,B.field) ) select field as [start],max(new_field) as [end] from cte_test group by field order by [start]/* 001 003 005 006 008 008 010 011 015 017 */ GO drop table test
where convert(int,A.new_field)+1= convert(int,B.field) 这句没看懂。求解释。
a 001
a 002
a 003b 005
就可以用分组取最小值,最大值来生成记录
create table test(field varchar(03))
insert into test values('001')
insert into test values('002')
insert into test values('003')
insert into test values('005')
insert into test values('006')
insert into test values('008')
insert into test values('010')
insert into test values('011')
insert into test values('015')
insert into test values('016')
insert into test values('017')go
;with cte_test
as
(
select field, field as new_field
from test as T
where not exists(select 1 from test where convert(int,field) = convert(int,T.field)-1)
union all
select A.field,B.field
from cte_test as A, test as B
where convert(int,A.new_field)+1= convert(int,B.field)
)
select field as [start],max(new_field) as [end]
from cte_test
group by field
order by [start]/*
001 003
005 006
008 008
010 011
015 017
*/
GO
drop table test
这句没看懂。求解释。
http://bbs.csdn.net/topics/320038203