Table A:
ID name grade
1 A 1
2 B 1
3 c 2
4 d 2
5 e 2
6 e 2
7 g 3
根据 name 和 grade 变为下列格式ID name grade flag
1 A 1 0
2 B 1 1
3 c 2 0
4 d 2 1
5 e 2 2
6 e 2 2
7 g 3 0
ID name grade
1 A 1
2 B 1
3 c 2
4 d 2
5 e 2
6 e 2
7 g 3
根据 name 和 grade 变为下列格式ID name grade flag
1 A 1 0
2 B 1 1
3 c 2 0
4 d 2 1
5 e 2 2
6 e 2 2
7 g 3 0
select id,name,grade,sum(num) num
from
(
select aaa.*,b.num from aaa left join (select name+grade as numid,sum(1) num from aaa group by name,grade) b
on aaa.name+aaa.grade=b.numid
union all
select aaa.*,c.num from aaa,(select grade,min(id) id,-1 num from aaa group by grade) c
where aaa.id=c.id
) n group by id,name,grade
发上来骗骗人(以上的行不通),建议LZ用临时表,不然比较费神,用临时表可以搞个cursor循环一下就可以得到flag:
cursor is select * from aaa order by grade,name;
flag从0开始,然后逢name不一样与grage一样加1,若grade变化则初始化flag为0,name与grade一样取同样的flag值