Mysql在having子句中传递变量

Question

我有这样的查询:

SELECT bp.id,
       bp.nomor_surat,
       bp.jumlah_setor,
       @sum := ROUND(SUM(
                     CASE
                         WHEN idc.vat = "Y"
                             THEN
                             (idc.nominal * idc.pcs) + (0.1 * ((idc.nominal * idc.pcs)))
                         ELSE (idc.nominal * idc.pcs)
                         END
                 ), 2)                      AS 'sum_invoice',
       @check := bp.jumlah_setor < ROUND(@sum,2) AS 'check',
       @kurang_bayar := ROUND(@sum,2) - bp.jumlah_setor AS 'insufficient_payment'

FROM bukti_penerimaan bp
         LEFT JOIN invoice i on bp.id = i.bukti_penerimaan_id
         LEFT JOIN invoice_detail id2 ON i.id = id2.invoice_id
         LEFT JOIN invoice_detail_child idc ON id2.id = idc.invoice_detail_id
GROUP BY bp.id
HAVING
    bp.jumlah_setor < ROUND(SUM(
        CASE
            WHEN idc.vat = "Y"
        THEN
            (idc.nominal * idc.pcs) + (0.1 * ((idc.nominal * idc.pcs)))
        ELSE (idc.nominal * idc.pcs)
        END
    ), 2)


+-----+-----------------------+--------------+--------------+-------+----------------------+
| id  | nomor_surat           | jumlah_setor | sum_invoice  | check | insufficient_payment |
+-----+-----------------------+--------------+--------------+-------+----------------------+
| 215 | 00002/JKT-TRM/2019/TS | 720078000.00 | 727078000.00 |     1 |           7000000.00 |
+-----+-----------------------+--------------+--------------+-------+----------------------+
1 row in set (0.00 sec)

当我将变量传递到查询中时，查询结果不一致:

SELECT bp.id,
       bp.nomor_surat,
       bp.jumlah_setor,
       @sum := ROUND(SUM(
                     CASE
                         WHEN idc.vat = "Y"
                             THEN
                             (idc.nominal * idc.pcs) + (0.1 * ((idc.nominal * idc.pcs)))
                         ELSE (idc.nominal * idc.pcs)
                         END
                 ), 2)                      AS 'sum_invoice',
       @check := bp.jumlah_setor < ROUND(@sum,2) AS 'check',
       @kurang_bayar := ROUND(@sum,2) - bp.jumlah_setor AS 'insufficient_payment'

FROM bukti_penerimaan bp
         LEFT JOIN invoice i on bp.id = i.bukti_penerimaan_id
         LEFT JOIN invoice_detail id2 ON i.id = id2.invoice_id
         LEFT JOIN invoice_detail_child idc ON id2.id = idc.invoice_detail_id
GROUP BY bp.id
HAVING
    @check = 1

我得到这样的结果:

+-----+-----------------------+--------------+--------------+-------+----------------------+
| id  | nomor_surat           | jumlah_setor | sum_invoice  | check | insufficient_payment |
+-----+-----------------------+--------------+--------------+-------+----------------------+
| 214 | 00001/JKT-TRM/2019/TS | 225347030.40 | 209147030.40 |     1 |         501730969.60 |
| 216 | 00003/JKT-TRM/2019/TS |  45793500.00 |  45793500.00 |     1 |         163353530.40 |
+-----+-----------------------+--------------+--------------+-------+----------------------+
2 rows in set (0.01 sec)

奇怪的是，我再次运行与上面相同的查询，我得到了空集(0.00 秒)

我的问题是，发生了什么？我应该在程序中创建它吗？

Answer 1

来自manual :

HAVING, GROUP BY, and ORDER BY, when referring to a variable that is assigned a value in the select expression list do not work as expected because the expression is evaluated on the client and thus can use stale column values from a previous row.

解决此问题的一种方法是将查询包装为子查询，并在 check 上添加 WHERE 子句:

SELECT *
FROM (SELECT bp.id,
             bp.nomor_surat,
             bp.jumlah_setor,
             @sum := ROUND(SUM(
                           CASE
                               WHEN idc.vat = "Y"
                                   THEN
                                   (idc.nominal * idc.pcs) + (0.1 * ((idc.nominal * idc.pcs)))
                               ELSE (idc.nominal * idc.pcs)
                               END
                       ), 2)                      AS 'sum_invoice',
             @check := bp.jumlah_setor < ROUND(@sum,2) AS 'check',
             @kurang_bayar := ROUND(@sum,2) - bp.jumlah_setor AS 'insufficient_payment'

      FROM bukti_penerimaan bp
               LEFT JOIN invoice i on bp.id = i.bukti_penerimaan_id
               LEFT JOIN invoice_detail id2 ON i.id = id2.invoice_id
               LEFT JOIN invoice_detail_child idc ON id2.id = idc.invoice_detail_id
      GROUP BY bp.id
    )
WHERE check = 1