MySQL-如何对同一列中的多个范围求和

Question

我已经断断续续地做了几天了。我必须阅读MySQL表(如下所述(来确定表中的组。代码列的值可以从01到99，这些值分为四个代码组。我目前以这种方式获取代码组：

SELECT SUM(code) AS code0 FROM codes WHERE code BETWEEN 1 AND 69 ;
SELECT SUM(code) AS code1 FROM codes WHERE code BETWEEN 70 AND 79 ;
SELECT SUM(code) AS code2 FROM codes WHERE code BETWEEN 80 AND 89 ;
SELECT SUM(code) AS code3 FROM codes WHERE code BETWEEN 90 AND 99 ;

但我真的希望能够在一个查询中完成这项工作。我试过CASE/WHEN/END，但似乎就是做不好。我试过很多版本，但这一个最接近：

(不工作，但关闭(

SELECT SUM(CASE code WHEN code BETWEEN 1 AND 69 THEN code ELSE 0 END) AS code0,
SUM(CASE code WHEN code BETWEEN 70 AND 79 THEN code ELSE 0 END) AS code1,
SUM(CASE code WHEN code BETWEEN 80 AND 89 THEN code ELSE 0 END) AS code2,
SUM(CASE code WHEN code BETWEEN 90 AND 99 THEN code ELSE 0 END) AS code3
FROM codes;

退货：

+-------+-------+-------+-------+
| code0 | code1 | code2 | code3 |
+-------+-------+-------+-------+
|     1 |     0 |     0 |     0 |
+-------+-------+-------+-------+

(这只是在计算‘01’条目？？？(

但是，有效的是我的四个单独的查询，它们返回：

SELECT SUM(code) AS code0 FROM codes WHERE code BETWEEN 1 AND 69 ;
+-------+
| code0 |
+-------+
|    55 |
+-------+
1 row in set (0.000 sec)
SELECT SUM(code) AS code1 FROM codes WHERE code BETWEEN 70 AND 79 ;
+-------+
| code1 |
+-------+
|   213 |
+-------+
1 row in set (0.000 sec)
SELECT SUM(code) AS code2 FROM codes WHERE code BETWEEN 80 AND 89 ;
+-------+
| code2 |
+-------+
|   326 |
+-------+
1 row in set (0.000 sec)
SELECT SUM(code) AS code3 FROM codes WHERE code BETWEEN 90 AND 99 ;
+-------+
| code3 |
+-------+
|  NULL |
+-------+

数据的东西--

codes table schema:
+-------------+-------------------------------+------+-----+---------+-------+
| Field       | Type                          | Null | Key | Default | Extra |
+-------------+-------------------------------+------+-----+---------+-------+
| code        | smallint(2) unsigned zerofill | NO   |     | 00      |       |
| descrip     | varchar(30)                   | NO   |     |         |       |
+-------------+-------------------------------+------+-----+---------+-------+

表格数据：

select * from codes;
+------+---------+
| code | descrip |
+------+---------+
|   01 | RWF     |
|   02 | BLK     |
|   03 | BWF     |
|   04 | CHAR    |
|   05 | RED     |
|   06 | XBRED   |
|   07 | MIXED   |
|   08 | HOLST   |
|   09 | JERSEY  |
|   10 | LGHRN   |
|   70 | WHT     |
|   71 | BLK     |
|   72 | SPOTTED |
|   80 | WHT     |
|   81 | BLK     |
|   82 | BLKFC   |
|   83 | WHTFC   |
+------+---------+

Answer 1

您的CASE语法不太正确，您将两种形式混合在一起，最终将code与布尔值进行比较，这就是为什么它只对code = 1的值求和。这是正确的形式：

SELECT SUM(CASE WHEN code BETWEEN  1 AND 69 THEN code ELSE 0 END) AS code0,
SUM(CASE WHEN code BETWEEN 70 AND 79 THEN code ELSE 0 END) AS code1,
SUM(CASE WHEN code BETWEEN 80 AND 89 THEN code ELSE 0 END) AS code2,
SUM(CASE WHEN code BETWEEN 90 AND 99 THEN code ELSE 0 END) AS code3
FROM codes;

输出：

code0   code1   code2   code3
55      213     326     0

根据您对该值使用情况的评论，使用COUNT或EXISTS进行以下查询会更有效：

SELECT COUNT(CASE WHEN code BETWEEN 1 AND 69 THEN code END) AS code0,
COUNT(CASE WHEN code BETWEEN 70 AND 79 THEN code END) AS code1,
COUNT(CASE WHEN code BETWEEN 80 AND 89 THEN code END) AS code2,
COUNT(CASE WHEN code BETWEEN 90 AND 99 THEN code END) AS code3
FROM codes
SELECT EXISTS (SELECT * FROM codes WHERE code BETWEEN 1 AND 69) AS code0,
EXISTS (SELECT * FROM codes WHERE code BETWEEN 70 AND 79) AS code1,
EXISTS (SELECT * FROM codes WHERE code BETWEEN 80 AND 89) AS code2,
EXISTS (SELECT * FROM codes WHERE code BETWEEN 90 AND 99) AS code3

dbfiddle 演示

Answer 2

即使您的方法很好，CASE语句的语法也不正确。修正后的版本是

SELECT SUM(CASE WHEN code BETWEEN 1 AND 69 THEN code ELSE 0 END) AS code0,
SUM(CASE WHEN code BETWEEN 70 AND 79 THEN code ELSE 0 END) AS code1,
SUM(CASE WHEN code BETWEEN 80 AND 89 THEN code ELSE 0 END) AS code2,
SUM(CASE WHEN code BETWEEN 90 AND 99 THEN code ELSE 0 END) AS code3
FROM codes;

经验法则是当你有一个直接的比较使用

CASE expression
WHEN test THEN result
…
ELSE otherResult
END

处理范围时使用

CASE
WHEN booleanExpression THEN result
…
ELSE otherResult
END

Answer 3

它应该可以工作。

SELECT 
*
FROM
(SELECT SUM(code) AS code0 FROM codes WHERE code BETWEEN 1 AND 69) t1,
(SELECT SUM(code) AS code1 FROM codes WHERE code BETWEEN 70 AND 79) t2,
(SELECT SUM(code) AS code2 FROM codes WHERE code BETWEEN 80 AND 89) t3,
(SELECT SUM(code) AS code3 FROM codes WHERE code BETWEEN 90 AND 99) t4