gpt4 book ai didi

php - 在sql中添加两列的值

转载 作者:行者123 更新时间:2023-11-29 05:25:18 24 4
gpt4 key购买 nike

我有一个类似于下面的代码

SELECT SUM(points_1) AS total_points_1, SUM(points_2) AS total_points_2,

total_points_1 +
CASE
WHEN total_points_1 BETWEEN 50 AND 100 THEN 50
WHEN total_points_1 BETWEEN 100 AND 200 THEN 100
WHEN total_points_1 BETWEEN 200 AND 300 THEN 200
WHEN total_points_1 BETWEEN 300 AND 400 THEN 300
ELSE 500
END AS another_row_1,

total_points_2 +
CASE
WHEN total_points_2 BETWEEN 50 AND 100 THEN 50
WHEN total_points_2 BETWEEN 100 AND 200 THEN 100
WHEN total_points_2 BETWEEN 200 AND 300 THEN 200
WHEN total_points_2 BETWEEN 300 AND 400 THEN 300
ELSE 500
END AS another_row_2,

FROM `table`

我的目标是添加 another_row_1another_row_2 的结果

我尝试在 END AS another_row_2, 之后执行下面的行,但没有成功

SUM(another_row_1 + another_row_2) AS total_sum

最佳答案

首先,为了减少困惑并提高可维护性,我建议将点重新计算逻辑(用 CASE 实现的逻辑)包装到一个函数中

CREATE FUNCTION add_points(_points INT)
RETURNS INT
RETURN
_points +
CASE
WHEN _points BETWEEN 50 AND 99 THEN 50
WHEN _points BETWEEN 100 AND 199 THEN 100
WHEN _points BETWEEN 200 AND 299 THEN 200
WHEN _points BETWEEN 300 AND 399 THEN 300
ELSE 500
END;

注意:您可能需要将数据类型从 INT 更改为 DECIMAL(n,m)(根据您的实际数据类型列 points_1points_2)

现在查询看起来像

SELECT total_points_1,
total_points_2,
another_total_1,
another_total_2,
another_total_1 + another_total_2 total_sum
FROM
(
SELECT SUM(points_1) total_points_1,
SUM(points_2) total_points_2,
add_points(SUM(points_1)) another_total_1,
add_points(SUM(points_2)) another_total_2
FROM table1
) q

示例输出:

| TOTAL_POINTS_1 | TOTAL_POINTS_2 | ANOTHER_TOTAL_1 | ANOTHER_TOTAL_2 | TOTAL_SUM ||----------------|----------------|-----------------|-----------------|-----------||            165 |            386 |             265 |             686 |       951 |

这是 SQLFiddle 演示

关于php - 在sql中添加两列的值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20879521/

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