MYSQL SUM、IF和结果为0 - MYSQL SUM, IF and result to be 0

MYSQL SUM, IF and result to be 0

本文关键字：结果 IF SUM MYSQL | 更新日期: 2023-09-27

我的Mysql语法有问题，我有类似表格的

GNT6988 rawabuaya   30000000
GNT3429 Purwacaraka 100000
GNT1326 CLARA       15000000
GNT9059 bensugih1   6100000
GNT9620 bensugih2   6100000
GNT9851 Abdulfattah 500000
GNT3927 SUKSES01    10000000
GNT4469 SUKSES02    10000000

我有类似的mysql语法

SELECT trx, SUM(jmlgf) AS jumlahgf
      FROM gf_start
      GROUP BY trx
      HAVING jumlahgf < 4000000 ORDER BY tgl ASC LIMIT 40

我得到了结果

GNT3429 100000
GNT9851 500000
GNT1405 1000000
GNT9660 100000
GNT7222 100000
GNT2407 2100000
GNT3383 100000
GNT5586 100000
GNT1419 100000
**i want to get result like**
GNT3429 100000
GNT9851 500000
GNT1405 1000000
GNT9660 0
GNT7222 0
GNT2407 0
GNT3383 0

如果SUM（jmlgf）>=16000000

我应该如何处理mysql语法？

i使用PHP MYSQL

使用一个变量，类似于以下内容：-

SELECT trx, @sum_so_far:=@sum_so_far + jumlahgf, IF(@sum_so_far >= 1600000, 0, jumlahgf) AS jumlahgf
FROM
(
    SELECT trx, SUM(jmlgf) AS jumlahgf
    FROM gf_start
    GROUP BY trx
    HAVING jumlahgf < 4000000 
    ORDER BY tgl ASC 
) sub0
CROSS JOIN
(
    SELECT @sum_so_far:=0
) sub1
LIMIT 40

或者将结果与另一个查询连接起来，以获得到目前为止的总和，类似于以下内容：-

SELECT trx, IF(rolling_cnt >= 1600000, 0, SUM(jmlgf)) AS jumlahgf
FROM gf_start
(
    SELECT a.trx, SUM(b.jmlgf) AS rolling_cnt
    FROM gf_start a
    INNER JOIN gf_start b
    ON a.trx = b.trx
    AND a.tgl <= b.tgl
    GROUP BY a.trx
) sub0
ON gf_start.trx = sub0.trx
GROUP BY trx
HAVING jumlahgf < 4000000 
ORDER BY tgl ASC 
LIMIT 40