我有以下mysql查詢：
SELECT pagos.id, codigo_factura as code, pagos.importe as amount
FROM pagos INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id)
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
ORDER BY pagos.id DESC
LIMIT 10 OFFSET 0
查詢回傳以下資料：
我想要的結果：
我希望如果有 2 條記錄具有相同的代碼，查詢將只回傳數量更大的記錄。
我嘗試了以下方法：
SELECT pagos.id, codigo_factura as code, pagos.importe as amount
FROM pagos INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id)
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
GROUP BY codigo_factura
HAVING pagos.importe > 0
ORDER BY pagos.id DESC
LIMIT 10 OFFSET 0
這對我不起作用，因為我希望在代碼不重復的情況下繼續出現具有空值的記錄。
有什么辦法嗎？
uj5u.com熱心網友回復：
SELECT codigo_factura as code, MAX(pagos.importe) as amount
FROM pagos
INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id)
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
GROUP BY code
將回傳最大值amountper codigo_factura。這些資料可用于id從表包的另一個副本中選擇相應的值：
SELECT MAX(pagos.id) id, codigo_factura as code, pagos.importe as amount
FROM pagos
INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id)
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
AND (codigo_factura, pagos.importe) IN (
SELECT codigo_factura as code, MAX(pagos.importe) as amount
FROM pagos
INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id)
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
GROUP BY code
)
GROUP BY code, amount;
外部查詢中的 MAX() 和 GROUP BY 允許修復相同且同時最大值amount匹配多個id相同的情況codigo_factura。可能 MIN() 對您來說更安全 - 在這種情況下進行編輯。
PS。您的代碼中有一半的列沒有表別名，并且您沒有提供表結構..可能有些表是多余的，上面的查詢可以簡化。
uj5u.com熱心網友回復：
在 MySQL 8.0 中，您可以ROW_NUMBER按如下方式使用視窗函式：
WITH cte AS (
SELECT pagos.id, codigo_factura as code, pagos.importe as amount,
ROW_NUMBER() OVER(PARTITION BY codigo_factura ORDER BY pagos.importe DESC) AS rn
FROM pagos INNER JOIN m_labores p ON (pagos.id_producto = p.id)
INNER JOIN maquinas m ON (pagos.id_maquina = m.id)
WHERE m.id = 561 AND fecha_registro BETWEEN '2022-10-11 00:00:00' AND '2022-10-18 23:59:59'
)
SELECT id, code, amount
FROM cte
WHERE rn = 1
ORDER BY pagos.id DESC
LIMIT 10 OFFSET 0
它將通過對“ pagos.importe ”進行排序（NULL 值首先出現）來為每個“ codigo_factura ”分配一個排名值。然后過濾掉所有排名與 1 不同的行將洗掉匹配“ codigo_factura ”的較小值。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/518127.html
上一篇：有條件地從同一表中的另一列更新列