mysql - "Invalid use of group function"当插入重复键更新时

Question

我的查询遇到问题，我想在重复时插入和更新该查询，但它却显示“组函数的使用无效”。

我运行了唯一的“Select”语句，并且没有出现“无效使用组功能”之类的问题。

这是我的完整代码:

INSERT INTO tbl_biir_aktual(cabang_kode, periode_thn, periode_bln, pending_pp_volume, pending_pp_value)
SELECT a.cabang_kode, YEAR(a.tanggal) AS tahun, MONTH(a.tanggal)AS bulan, 
    SUM(a.qty_pending*a.unit_barang)AS tonase_pending, SUM(a.value_pending)AS value_pending 
FROM tbl_order a,
    (SELECT b.cabang_kode, MAX(b.tanggal)tanggal
    FROM tbl_order b
    GROUP BY b.cabang_kode, YEAR(b.tanggal), MONTH(b.tanggal)) AS max_cabang
WHERE max_cabang.cabang_kode = a.cabang_kode AND max_cabang.tanggal = a.tanggal
GROUP BY cabang_kode, YEAR(tanggal), MONTH(tanggal)
ON DUPLICATE KEY
UPDATE pending_pp_volume = SUM(a.qty_pending*a.unit_barang), pending_pp_value = SUM(a.value_pending);

嗯，嘿我刚刚发现这个MySQL ON DUPLICATE KEY UPDATE while inserting a result set from a query

INSERT INTO tbl_biir_aktual(cabang_kode, periode_thn, periode_bln, pending_pp_volume, pending_pp_value)
SELECT a.cabang_kode, YEAR(a.tanggal) AS tahun, MONTH(a.tanggal)AS bulan, 
    @tonase_pending := SUM(a.qty_pending*a.unit_barang)AS tonase_pending, @value_pending := SUM(a.value_pending)AS value_pending 
FROM tbl_order a,
    (SELECT b.cabang_kode, MAX(b.tanggal)tanggal
    FROM tbl_order b
    GROUP BY b.cabang_kode, YEAR(b.tanggal), MONTH(b.tanggal)) AS max_cabang
WHERE max_cabang.cabang_kode = a.cabang_kode AND max_cabang.tanggal = a.tanggal
GROUP BY cabang_kode, YEAR(tanggal), MONTH(tanggal)
ON DUPLICATE KEY
UPDATE pending_pp_volume = @tonase_pending, pending_pp_value = @value_pending;

我已经尝试过了，已经完成了。无论如何，请听 Edper 的评论......

Answer 1

尝试在更新中使用别名:

UPDATE SET pending_pp_volume = tonase_pending, pending_pp_value = value_pending

此外，为聚合别名使用其他名称比使用与现有字段名称(例如 value_pending)相同的名称更明智。不妨将其更改为 TotalValuePending，这样它将是:

UPDATE SET pending_pp_volume = tonase_pending, pending_pp_value = TotalValuePending