Mysql在clause子句中传递一个变量

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)

奇怪的是，我再次在与上面相同的查询中运行，得到了Empty set (0.00 sec)

我的问题是，发生了什么事？我应该在一个过程中创建这个过程吗？

Answer 1

来自手册

当引用在select表达式列表中赋值的变量时，拥有、分组和ORDER不按预期工作，因为表达式是在客户端上计算的，因此可以使用来自上一行的陈旧列值。

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

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