在Mysq中，如何从点表中找到范围？
在Mysq中，如何从点表中找到范围？
提问于 2013-11-02 08:31:34
如果增量值大于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 |
+----+----------+----------+----------+----------+------------+类似的案例场景，以使其更易于理解
我之前生成的范围表是结果I want.lets，说othertbl是产品表，每个产品在每个点范围内都有不同的库存计数，假设t恤A的年龄为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 |
+----+----------+----------+----------+----------+------------+回答 1
Stack Overflow用户
发布于 2013-11-02 08:47:32
问题不是很清楚，不管怎么说，这是一个变体，它返回的“某物”非常接近预期的结果：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19737612
复制相关文章
相似问题
腾讯云开发者
