ID groups Pages Line
-------------------- ------ ----------- -----------
1 A 1 1
2 A 1 1
3 A 1 1
4 A 1 1
5 A 1 1
6 A 1 1
7 A 1 1
8 B 1 1
9 B 1 1
10 B 1 1
11 C 1 1
12 C 1 1结果
ID Groups Pages Line
-------------------- ------ ----------- --------------------
1 A 1 1
2 A 1 2
3 A 1 3
4 A 1 4
5 A 1 5
6 A 2 1
7 A 2 2
8 B 3 1
9 B 3 2
10 B 3 3
11 C 4 1
12 C 4 2
-------------------- ------ ----------- -----------
1 A 1 1
2 A 1 1
3 A 1 1
4 A 1 1
5 A 1 1
6 A 1 1
7 A 1 1
8 B 1 1
9 B 1 1
10 B 1 1
11 C 1 1
12 C 1 1结果
ID Groups Pages Line
-------------------- ------ ----------- --------------------
1 A 1 1
2 A 1 2
3 A 1 3
4 A 1 4
5 A 1 5
6 A 2 1
7 A 2 2
8 B 3 1
9 B 3 2
10 B 3 3
11 C 4 1
12 C 4 2
麻烦耐心看看它们的规律.
B少于5个,分在一页里,C少于5条也只要一页,line为页内编号
select id,groups,pages+groupsord/6 as pages,case when groupsord%5=0 then 5 else lines+groupsord%5-1 end as lines
from
(select x.*,x.id-y.minid+1 as groupsord
from tmp x join (select groups,min(id) as minid from tmp group by groups) y on x.groups=y.groups)m
只有测试过你上面提供的资料,没做更多的测试
select id,groups,groupsord/6+minid/6+sortid as pages,case when groupsord%5=0 then 5 else lines+groupsord%5-1 end as lines
from
(
select x.*,x.id-y.minid+1 as groupsord,sortid,minid,maxid
from tmp x join
(select a.groups,a.minid,a.maxid,count(*) as sortid
from
(select groups,min(id) as minid,max(id) as maxid from tmp group by groups) a,
(select groups,min(id) as minid,max(id) as maxid from tmp group by groups) b
where a.groups>=b.groups
group by a.groups,a.minid,a.maxid)y on x.groups=y.groups
) m