mysql—在sql中求两列之和，其中一列需要求反

dzhpxtsq 于 2021-06-18 发布在 Mysql

关注(0)|答案(3)|浏览(333)

我想对sql中的两列求和，如果和大于0，则将其输出到其中一列，如果小于0，则输出到另一列。
我的代码如下所示：

SELECT IF(SUM(`processed_quantity_long`,-1*`processed_quantity_short`) > 0,SUM(`processed_quantity_long`,-1*`processed_quantity_short`) AS `Position Long`,SUM(`processed_quantity_long`,-1*`processed_quantity_short`) AS `Position Short`) 
From table A 
GROUPBY date

它将返回以下错误：
sql语法有错误；检查与您的mariadb服务器版本对应的手册，以获取在'-1'附近使用的正确语法* processed_quantity_short )>0，总和( processed_quantity_long ，-1*“第5行已处理”
不确定如何解决此错误。

sql mysql mariadb

来源：https://stackoverflow.com/questions/53204997/sum-two-columns-in-sql-where-one-columns-needs-to-be-negated

3条答案

按热度按时间

jum4pzuy1#

SUM 不接受两个参数。在计算和之前，只需减去这两个数字：

SELECT
    CASE WHEN SUM(processed_quantity_long - processed_quantity_short) >= 0 THEN SUM(processed_quantity_long - processed_quantity_short) END AS `Position Long`,
    CASE WHEN SUM(processed_quantity_long - processed_quantity_short) <  0 THEN SUM(processed_quantity_long - processed_quantity_short) END AS `Position Short`
FROM tablea
GROUP BY date

赞(0）回复(0）举报 2021-06-18

deyfvvtc2#

你在里面做错了如果

SELECT IF(
          SUM(processed_quantity_long-processed_quantity_short) > 0,
          SUM(`processed_quantity_long`-`processed_quantity_short`) ,
          SUM(`processed_quantity_long`-`processed_quantity_short`)  AS  `Position Short`
    ) From tableA GROUP BY date

如果语句如下所示，则为一般语句

SELECT IF(500<1000, "YES", "NO")

或用例

case when SUM(`processed_quantity_long`-`processed_quantity_short`) > 0
 then SUM(`processed_quantity_long`-`processed_quantity_short`)
 else SUM(`processed_quantity_long`-`processed_quantity_short`) as position
from tableA GROUP BY date

赞(0）回复(0）举报 2021-06-18

mo49yndu3#

也许编写代码最简单的方法是：

SELECT GREATEST(SUM(processed_quantity_long - processed_quantity_short), 0) AS Position_Long,
       LEAST(SUM(processed_quantity_long - processed_quantity_short), 0) AS Position_Short
FROM tablea
GROUP BY date

赞(0）回复(0）举报 2021-06-18

我来回答

mysql—在sql中求两列之和，其中一列需要求反

3条答案

相关问题

热门标签

最新问答