急求一条较难的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
) t
group by name
