主页 / dba / 问题 / 334744
Accepted
cem ekkazan
Asked: 2024-01-11 02:41:37 +0800 CST

将聚合函数的结果与列进行比较

  • 772

我有两张这样的桌子；

发票

id | total_amount | currency | discount

付款

id | invoice | amount

正如您所猜测的，付款表保存对特定发票的付款记录。

我需要查询以获取已部分支付的发票；

SELECT
    invoices.*,
    SUM(payments.amount) as paid
FROM
    invoices
LEFT JOIN
    payments ON payments.invoice = invoices.id
GROUP BY
    invoices.id

我能够通过此查询获得付款金额，但我也需要做类似的事情

WHERE paid < total_amount AND paid > 0

但这表示“未知列在‘where 子句’中‘付费’”。我怎样才能得到这份工作？

aggregate

2 个回答

  1. 聚合结果不会出现在 WHERE 子句中，而是出现在 HAVING 子句中：

    SELECT
    invoices.*, --as noted in comment, any desired fields from here must be in the GROUP BY
    SUM(payments.amount) as paid 
FROM
    invoices 
LEFT JOIN
    payments ON payments.invoice = invoices.id 
GROUP BY
    invoices.id 
HAVING SUM(payments.amount) > 0 AND SUM(payments.amount) < invoice.total_amount

    编辑：如果您的目的是获取发票中的所有字段，而仅获取付款中支付的总额，您可以尝试子选择：

    SELECT
    invoices.*, 
    p.paid 
FROM
    invoices 
LEFT JOIN
    (SELECT invoice, SUM(amount) as paid
     FROM payments 
     GROUP BY invoice
    ) p ON p.invoice = invoices.id 
WHERE p.paid > 0 AND p.paid < invoice.total_amount
    • 1
  2. Best Answer

    这是一种方法：

    SELECT
    x.*
FROM 
(
    SELECT
        invoices.*,
        SUM(payments.amount) as paid
    FROM
        invoices
    LEFT JOIN
        payments ON payments.invoice = invoices.id
    GROUP BY
        invoices.id
) AS x
WHERE x.paid < x.total_amount 
AND   x.paid > 0;

    由于逻辑查询处理的工作方式，作为选择列表中的表达式的列（如聚合）不能在它们之前处理的查询部分中使用。

    • 0

相关问题