我有下表。
表名：机器
id machine_type current_usage max_capacity
1 1 25 500
2 1 20 500
3 2 300 400
...
编写查询未满负荷机器的数量很容易。
SELECT machine_type, count(*) FROM Machine WHERE ROUND((current_usage/max_capacity)*100, 2) < 100.00 GROUP BY machine_type;
但是我正在尝试编写SQL查询以以下格式输出数据
machine_type thirty_percent sixty_percent more_than_sixty
1 25 40 50
2 40 15 25
3 60 10 55
...
thirty_percent = Number of machines whose usage is <= 30% usage
sixty_percent = Number of machines whose usage is > 30% and <=60% usage
more_than_sixty = Number of machines whose usage is > 60% and < 100% usage
我可以为每个使用窗口修改查询，但这将是三个语句。有没有办法在单个SQL语句中执行此操作？
您可以使用条件聚合：
SELECT machine_type,
SUM(current_usage / max_capacity < 0.30) as percent_30,
SUM(current_usage / max_capacity >= 0.3 and current_usage / max_capacity < 0.60) as percent_60,
SUM(current_usage / max_capacity >= 0.60) as percent_60_plus
FROM Machine
GROUP BY machine_type;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句