gpt4 book ai didi

mysql - 在 MySQL 中求和

转载 作者:行者123 更新时间:2023-11-29 04:27:51 25 4
gpt4 key购买 nike

我有这个假设的表格:

+----+---------+-------+
| 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 |
+------+------+

最佳答案

试试这个:

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)

关于mysql - 在 MySQL 中求和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8073317/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com