mysql - 在Mysql中我怎样才能找到点表的范围？

Question

如果增量值大于 0.25，如果范围与花药分开，我如何从点表中找到可用范围(我有积分表，我想生成范围表)

POINTS table
+----+------+------+------------+
| id | sph  |  cyl | othertbl_id|
+----+------+------+------------+
|  1 | 0    | 0    |     1      |
|  2 | 0    | 0.25 |     1      |
|  3 | 0    | 0.50 |     1      |
|  4 | 0    | 1.00 |     1      |
|  5 | 0.25 | 0    |     1      |
|  6 | 0.25 | 0.25 |     1      |
|  7 | 0.25 | 0.50 |     1      |
|  8 | 0.25 | 1.00 |     1      |
|  9 | 3.25 | 0    |     1      |
| 10 | 3.25 | 0.25 |     1      |
| 11 | 3.50 | 0    |     1      |
| 12 | 3.50 | 0.25 |     1      |
| 13 | 3.75 | 0    |     1      |
| 14 | 3.75 | 0.25 |     1      |
+----+-------------+------------+

我想生成如下所示的范围表

+----+----------+----------+----------+----------+------------+
| id | min_sph  |  max_sph | min_cyl  |  max_cyl |othertbl_id |
+----+----------+----------+----------+----------+------------+
|  1 | 0        | 0.25     |     0    |  1.00    |      1     |
|  2 | 3.25     | 3.75     |     0    |  0.25    |      1     |
+----+----------+----------+----------+----------+------------+

类似的案例场景使这更容易理解

我之前生成的范围表是我想要的结果。假设 othertbl 是产品表，每个产品在每个点范围内都有不同的库存数量，假设 T 恤 A 的年龄为 0-2 岁，高度为 0-2 岁。 30-50 岁，也适用于 28-30 岁、高度 100-120 岁，所有这些范围的点都适用于同一 T 恤排。如果年龄相差超过一年或高度相差超过 10 则被视为新范围

在这种情况下，积分表将如下所示

+----+------+------+------------+
| id | age  |  hgt | othertbl_id|
+----+------+------+------------+
|  1 | 0    | 30   |     1      |
|  2 | 0    | 40   |     1      |
|  3 | 0    | 50   |     1      |
|  4 | 1    | 30   |     1      |
|  5 | 1    | 40   |     1      |
|  6 | 1    | 50   |     1      |
|  7 | 2    | 30   |     1      |
|  8 | 2    | 40   |     1      |
|  9 | 2    | 50   |     1      |
| 10 | 28   | 100  |     1      |
| 11 | 28   | 110  |     1      |
| 12 | 28   | 120  |     1      |
| 13 | 29   | 100  |     1      |
| 14 | 29   | 110  |     1      |
| 14 | 29   | 120  |     1      |
| 14 | 30   | 100  |     1      |
| 14 | 30   | 110  |     1      |
| 14 | 30   | 120  |     1      |
+----+-------------+------------+

范围表将如下所示

+----+----------+----------+----------+----------+------------+
| id | min_age  |  max_age | min_hgt  |  max_hgt |othertbl_id |
+----+----------+----------+----------+----------+------------+
|  1 | 0        | 2        |     30   |  50      |      1     |
|  2 | 28       | 30       |     100  |  120     |      1     |
+----+----------+----------+----------+----------+------------+

Answer 1

问题不是很清楚，无论如何，这里有一个变体，它返回非常接近期望结果的“某物”:

select min(id) as id, sph as min_sph, (select min(sph) from points as b where b.sph>a.sph) as max_sph, min(cyl) as min_cyl, max(cyl) as max_cyl, othertbl_id as othertbl_id
from points as a
group by othertable_id, sph

结果是:

id  minsph  maxsph  mincyl  maxcyl  othertable_id
1   0       0.25    0       1       1
5   0.25    3.25    0       1       1
9   3.25    3.5     0       0.25    1
11  3.5     3.75    0       0.25    1
13  3.75    (null)  0       0.25    1