SQL:根据多个条件计算行数,单个查询

您似乎在寻找条件聚合:

SELECT 
    count(*) total,
    SUM(CASE WHEN score BETWEEN 0.1 AND 0.3  THEN 1 ELSE 0 END) low,
    SUM(CASE WHEN score BETWEEN 0.3 AND 0.6  THEN 1 ELSE 0 END) medium,
    SUM(CASE WHEN score BETWEEN 0.6 AND 0.95 THEN 1 ELSE 0 END) high,
    SUM(CASE WHEN score >= 0.95 THEN 1 ELSE 0 END) 'very_high'
FROM my_table

此查询将返回一行,显示分析的总行数和属于每个类别的行数。 col BETWEEN val1 AND val2 是的快捷方式 col >= val1 AND col < val2 .

你的方法非常接近,你可以在你的条件下使用。

这就可以做到:

SELECT COUNT(*),
CASE WHEN score > 0.1 AND score <= 0.3 THEN "low"
WHEN score > 0.3 AND score <= 0.6 THEN "medium"
WHEN score > 0.6 AND score <= 0.95 THEN "high"
WHEN score > 0.95 THEN "very_high"
ELSE "very_low"
END AS score_group
FROM my_table
GROUP BY score_group