mysql - 对值进行分桶，直到达到 max Buck sql

Question

我正在尝试根据最大小时数将一个事件放入多个存储桶中。在我的示例中，Pin 这里的最大小时数为 1938。因此，仅通过我的查询将其存储到 2000 范围。但理想情况下，由于大头针的使用时间已达到 1938 小时，因此它在过去的某个时间肯定已经使用了 500、1000 和 1500 小时。因此，我正在寻找一个查询，该查询不仅会在 <= 2000 存储桶中分配此引脚，而且会在 500、1000、1500 和 2000 存储桶中分配此引脚。因此，基本上逻辑应该在所有存储桶中分配 pin，直到它达到最大的小时存储桶。请帮忙。

我的查询

SELECT *,
CASE WHEN "MAX HOURS" <= 500 THEN '500' 
 WHEN "MAX HOURS" <=1000  THEN '1000'
 WHEN "MAX HOURS" <=1500 THEN '1500'
 WHEN "MAX_HOURS" <=2000 THEN '2000'
 WHEN "MAX_HOURS" <=2500 THEN '2500'
 WHEN "MAX_HOURS" <=3000 THEN '3000' 
 WHEN "MAX_HOURS" <=3500 THEN '3500' 
 WHEN "MAX_HOURS" <=4000 THEN '4000' 
 WHEN "MAX_HOURS" <=4500 THEN '4500' 
 WHEN "MAX_HOURS" <=5000 THEN '5000'
ELSE 'OVER 5000'
END AS Hours_Bucket
FROM PA_BTC

[当前输出]

PIN   Max hours  Hours_Bucket
XYZ   1938.10       2000

以下是我所期望的:对于所有引脚。我不确定是否以及如何创建一个循环来创建以下所需的输出。[预期结果]

PIN  Max hours  Hours_Bucket
XYZ  1938            500
XYZ  1938           1000
XYZ  1938           1500
XYZ  1938           2000

Answer 1

我讨厌这个查询的样子，但我不确定还有其他方法可以做到这一点

SELECT PIN, `MAX HOURS`, 500 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 0
UNION ALL
SELECT PIN, `MAX HOURS`, 1000 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 500
UNION ALL
SELECT PIN, `MAX HOURS`, 1500 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 1000
UNION ALL
SELECT PIN, `MAX HOURS`, 2000 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 1500
UNION ALL
SELECT PIN, `MAX HOURS`, 2500 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 2000
UNION ALL
SELECT PIN, `MAX HOURS`, 3000 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 2500
UNION ALL
SELECT PIN, `MAX HOURS`, 3500 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 3000
UNION ALL
SELECT PIN, `MAX HOURS`, 4000 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 3500
UNION ALL
SELECT PIN, `MAX HOURS`, 4500 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 4000
UNION ALL
SELECT PIN, `MAX HOURS`, 5000 AS Hours_Bucket
FROM PA_BTC
WHERE `MAX HOURS` > 4500