急求一条较难的SQL语句~~~~~~在线等~~

一个test表,字段如下:

id                 name
2                     A
3                     A
4                     A
6                     B
8                     C
12                   D
14                   D
15                   E
16                   A
17                   A
18                   B
----------------
显示结果
name         最高连续次数
A                   3
B                   2
C                   1
D                   2
E                   1
我要求连续出现次数最高的个数
如:
A   从id=2到4   连续出现3次
A   从id=16到17   出现2次
A的最高是3次
B   id=6         出现1次
B   id=18       出现1次
B的最高是1次
其它同理

------解决方案--------------------
create table T(id int, name varchar(10))
insert T select 2, 'A '
union all select 3, 'A '
union all select 4, 'A '
union all select 6, 'B '
union all select 8, 'C '
union all select 12, 'D '
union all select 14, 'D '
union all select 15, 'E '
union all select 16, 'A '
union all select 17, 'A '
union all select 18, 'B '


select A.name,
起始号=A.id,
终止号=MIN(B.id),
num=( select count(*) from T where id between A.id and min(B.id) )
from
(
select tmp.* from T as tmp
where not exists(select 1 from T where name=tmp.name and id=(select max(id) from T where id <tmp.id) )
) as A,
(
select tmp.* from T as tmp
where not exists(select 1 from T where name=tmp.name and id=(select min(id) from T where id> tmp.id) )
) as B
where A.name=B.name and A.id <=B.id
group by A.name, A.id

--result
name 起始号 终止号 num
---------- ----------- ----------- -----------
A 2 4 3
B 6 6 1
C 8 8 1
D 12 14 2
E 15 15 1
A 16 17 2
B 18 18 1

(7 row(s) affected)
------解决方案--------------------

select name,max(Total) total from
(
select name,min(id) minID,max(id) maxID,count(*) Total from
(
select a.name,a.id,count(*) - sum(case when a.name = b.name then 1 else 0 end) groupid
from z a,z b
where a.id > = b.id
group by a.name,a.id
) t
group by name,groupid