在MySQL中总结(Summing in MySQL)

在MySQL中总结(Summing in MySQL)

我有这个假设的表格：

+----+---------+-------+ | id | type_id | value | +----+---------+-------+ | 1 | 1 | 10 | | 2 | 1 | 20 | | 3 | 2 | 30 | | 4 | 2 | 40 | | 5 | 3 | 50 | | 6 | 3 | 60 | | 7 | 4 | 70 | | 8 | 4 | 80 | | 9 | 4 | 90 | | 10 | 4 | 100 | +----+---------+-------+

我想要做的是将所有值与类型1,2,3和所有值类型4相加：

+------+------+ | s123 | s4 | +------+------+ | 210 | 340 | +------+------+

如何在不使用嵌套查询和UNION的情况下执行此操作？ 我尝试了以下查询，结果错误：

SELECT SUM(t1.value) AS s123, SUM(t2.value) AS s4 FROM test AS t1, test AS t2 WHERE t1.type_id IN (1,2,3) AND t2.type_id=4 +------+------+ | s123 | s4 | +------+------+ | 840 | 2040 | +------+------+

I have this hypothetical table:

+----+---------+-------+ | id | type_id | value | +----+---------+-------+ | 1 | 1 | 10 | | 2 | 1 | 20 | | 3 | 2 | 30 | | 4 | 2 | 40 | | 5 | 3 | 50 | | 6 | 3 | 60 | | 7 | 4 | 70 | | 8 | 4 | 80 | | 9 | 4 | 90 | | 10 | 4 | 100 | +----+---------+-------+

What I want to do is sum all values with types 1,2,3 and all values with type 4:

+------+------+ | s123 | s4 | +------+------+ | 210 | 340 | +------+------+

How can I do this without using nested queries and UNION? I tried the following query which gave the wrong result:

SELECT SUM(t1.value) AS s123, SUM(t2.value) AS s4 FROM test AS t1, test AS t2 WHERE t1.type_id IN (1,2,3) AND t2.type_id=4 +------+------+ | s123 | s4 | +------+------+ | 840 | 2040 | +------+------+

最满意答案

尝试这个：

SELECT SUM(CASE WHEN t1.type_id = 4 THEN 0 ELSE t1.value END) AS s123, SUM(CASE WHEN t1.type_id = 4 THEN t1.value ELSE 0 END) AS s4 FROM test AS t1 WHERE t1.type_id IN (1,2,3, 4)

Try this:

SELECT SUM(CASE WHEN t1.type_id = 4 THEN 0 ELSE t1.value END) AS s123, SUM(CASE WHEN t1.type_id = 4 THEN t1.value ELSE 0 END) AS s4 FROM test AS t1 WHERE t1.type_id IN (1,2,3, 4)