--试试
create table #a(id int,name varchar(10),num)insert #a(id,name)
select * from test
order by iddeclare @num int,@name varchar(10)update #a
set @num = case when name = @name then @num + 1 else 1 end,
@name = name,num = @numselect name,max(num)
from #a
group by name
create table #a(id int,name varchar(10),num)insert #a(id,name)
select * from test
order by iddeclare @num int,@name varchar(10)update #a
set @num = case when name = @name then @num + 1 else 1 end,
@name = name,num = @numselect name,max(num)
from #a
group by name
急求~~~`
B只有1次
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)
能不能这梓显示
A 2 ,4 3
B 6,6 1
C 8,8 1
D 12,14 2
E 15,15 1
急求,测试成功!马上结贴~~
A 3
B 1
C 1
D 2
E 1
(
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--------------这样也行,:)
不过也较慢
我不太会SQL.不知道怎样修改~~
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
) t
group by name
问题终于得到了解决~
5分内结贴~~~