具有限制N分组的Mysql查询最小值和最大值

I'd like to ask some question about query.

我想问一些关于查询的问题。

This is my case:

这是我的情况：

Structure Table

codenumber  varchar (PK)
prize       varchar
batchno     double
category    varchar

Sample Data On Database:

数据库示例数据：

Code        Prize       BatchNumber Category
1000000231  TRY AGAIN   1           A
1000000238  TRY AGAIN   2           A
1000000376  TRY AGAIN   3           A
1000000473  TRY AGAIN   4           A
1000000934  50          5           A
1000001281  50          6           B
1000001894  50          7           B
1000002014  TRY AGAIN   8           B
1000002831  TRY AGAIN   9           B
1000003123  TRY AGAIN   10          B
1000003158  TRY AGAIN   11          C
1000003224  TRY AGAIN   12          C
1000003524  TRY AGAIN   13          C
1000003598  50          14          C
1000003616  TRY AGAIN   15          C
1000003657  TRY AGAIN   16          A
1000003959  50          17          A
1000004289  TRY AGAIN   18          A
1000004529  TRY AGAIN   19          A
1000004853  TRY AGAIN   20          A
1000005683  TRY AGAIN   21          B
1000005728  100         22          B
1000005816  TRY AGAIN   23          B
1000006325  TRY AGAIN   24          B

I wanted to get the Minimum and Maximum batch number for each 5 rows. Then how to get the query result like below:

我想获得每5行的最小和最大批号。然后如何得到如下的查询结果：

Category        MinBatch        MaxBatch
A               1               5
B               6               10
C               11              15
A               16              20
B               21              24

Please Help Thanks

请帮助谢谢

2 个解决方案

#1

Presuming that batch represents the ordering for determining groups of 5, you can do this with variables:

假设批处理表示确定5的组的顺序，您可以使用变量执行此操作：

select category, min(batch), max(batch)
from (select s.*, (@rn := @rn + 1) as rn
      from structure s cross join
           (select @rn := 0) params
      order by batch
     ) s
group by floor((rn - 1) / 5)
order by min(batch);

Actually, if you know the batches are consecutive with no gaps and start at 1:

实际上，如果您知道批次是连续的，没有间隙并从1开始：

select category, min(batch), max(batch)
from structure s
group by floor((batch - 1) / 5)
order by min(batch);

#2

Below query will give you the result

下面的查询将为您提供结果

select category, min(batchnumber)as 'MinBatch', max(batchnumber)as 'MaxBatch'
from tablename order group by (category)

#1