跪求答案，着急，请提示如何写这个sql语句？感恩不尽

[kelikeli521]

d1 2803 甲
d1 2804 甲
d1 2805 乙
d1 2806 乙
d1 2807 乙
d1 2808 甲
d1 2809 甲
d1 28010 甲

输出结果为
起始号 终止号 个数 人物
d1 2803 2804 2 甲
d1 2805 2807 3 乙
d1 2808 28010 3 甲

[kelikeli521]

ding bangding

[-狙击手-]

------------------------------------
-- Author: flystone 
-- Version:V1.001  
-- Date:2010-12-21  
------------------------------------

-- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
Go
Create table ta(c1 nvarchar(2),c2 nvarchar(6),c3 nvarchar(1))
Go
Insert into ta
 select 'd1','2803','甲' union all
 select 'd1','2804','甲' union all
 select 'd1','2805','乙' union all
 select 'd1','2806','乙' union all
 select 'd1','2807','乙' union all
 select 'd1','2808','甲' union all
 select 'd1','2809','甲' union all
 select 'd1','28010','甲' 
Go
--Start
alter table ta add c4 int
go
declare @i int,@c3 varchar(10)
set @I = 1
update ta
set c4 = @i,@i = case when c3 != @c3 then @i+1 else @I end,@c3 = c3
Select c1,min(c2),max(c2),count(1),c3
from ta
group by c1,c3,c4


--Result:
/*


c1                             c3   
---- ------ ------ ----------- ---- 
d1   2803   2804   2           甲
d1   28010  2809   3           甲
d1   2805   2807   3           乙

（所影响的行数为 3 行）

*/
--End 

[kelikeli521]

还有没有简单点的
我的数据量巨大啊
这么整不行啊

[coleling]

create table tb(a varchar(10),b int, c varchar(10))
insert tb select 'd1', 2803, '甲'
insert tb select 'd1', 2804, '甲'
insert tb select 'd1', 2805, '乙'
insert tb select 'd1', 2806, '乙'
insert tb select 'd1', 2807, '乙'
insert tb select 'd1', 2808, '甲'
insert tb select 'd1', 2809, '甲'
insert tb select 'd1', 28010, '甲'
go

with t1 as
(
	select rid = row_number() over(order by getdate()), gid = row_number() over(partition by a,c order by b),* from tb
)
select a,起始号=min(b),终止号=max(b),个数=count(1),人物=c from t1 group by rid-gid,a,c order by rid-gid

/*
a          起始号         终止号         个数          人物
---------- ----------- ----------- ----------- ----------
d1         2803        2804        2           甲
d1         2805        2807        3           乙
d1         2808        28010       3           甲

(3 行受影响)
*/

记得结贴哦

[coleling]

[Quote=引用 2 楼 happyflystone 的回复:]
declare @i int,@c3 varchar(10)
set @I = 1
update ta
set c4 = @i,@i = case when c3 != @c3 then @i+1 else @I end,@c3 = c3
[/Quote]

这一段写的真是相当的风骚啊，PFPF

[王向飞]

[soulJava]

create table #t(t1 varchar(10),t2 int,t3 varchar(20))
insert into #t 
select 'd1',2803,'甲' union all
select 'd1',2804,'甲' union all
select 'd1',2805,'乙' union all
select 'd1',2806,'乙' union all
select 'd1',2807,'乙' union all
select 'd1',2808,'甲' union all
select 'd1',2809,'甲' union all
select 'd1',2810,'甲' 

with a1 as(
select *,rowid=row_number() over(order by getdate()) from #t where not exists(select 1 from #t t1 where #t.t3=t1.t3 and #t.t2=(t1.t2-1))
),a2 as(
select *,rowid=row_number() over(order by getdate()) from #t where not exists(select 1 from #t t1 where #t.t3=t1.t3 and #t.t2=(t1.t2+1))
)select a1.t1,a2.t2 as 起始编号,a1.t2 as 结束编号,(a1.t2-a2.t2+1) as 人数,a1.t3 as 人物 from a1 inner join a2 on a1.t1=a2.t1 and a1.t3=a2.t3 and a1.rowid=a2.rowid

[yhtapmys]

编号有点

--create data
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test2]') 
AND type in (N'U')) 
DROP TABLE [test2]
GO
CREATE   TABLE   test2( 
typ   VARCHAR(10),          
num   VARCHAR(10),               
tag   VARCHAR(10)
) 
GO
insert into test2 
select 'd1','2803','A' union all
select 'd1','2804','A' union all
select 'd1','2805','B' union all
select 'd1','2806','B' union all
select 'd1','2807','B' union all
select 'd1','2808','A' union all
select 'd1','2809','A' union all
select 'd1','28010','A'
go

--Execute
select max(typ),min(Convert(int,t.num)) 起始号,max(Convert(int,t.num)) 终止号,COUNT(1) as 个数,max(tag) 人物
from (select typ,num,tag,rownum=(select count(tag) 
                from test2 
                where tag=a.tag and Convert(int,substring(num,4,len(num)-3))<Convert(int,substring(a.num,4,len(a.num)-3)))  
      from test2 a) t
group by  t.typ, Convert(int,substring(num,4,len(num)-3))-rownum

--results
/*
    起始号 终止号 个数 人物
--------------------------------
d1	2803	2804	2	A
d1	2805	2807	3	B
d1	2808	28010	3	A
*/

--Drop table test2

[kelikeli521]

谢谢大家

[kelikeli521]

谢谢大家 结贴了，分已经给了