mysql - 使用大小写按计算列进行过滤和排序(未找到列)

Question

我尝试使用大小写按计算列进行过滤和排序，但出现语法错误。

Column difference_sum and column payments_sum not found

我需要能够有 WHERE 和 ORDER BY 子句。

我有两个表“契约(Contract)”和“付款”。

契约(Contract)表

+----+-----------------+-----------+-----------+
| id | contract_number | legal_sum | bonus_sum |
+----+-----------------+-----------+-----------+
|  1 |    110258465651 |        50 |        20 |
|  2 |    564984656355 |        15 |        12 |
|  3 |    548498415165 |       150 |        35 |
+----+-----------------+-----------+-----------+

付款表

+----+--------------+----------+
| id | contract_id  | paid_sum |
+----+--------------+----------+
|  1 | 564984656355 |        7 |
|  2 | 564984656355 |        1 |
|  3 | 564984656355 |        2 |
+----+--------------+----------+

1. 首先我需要计算法定金额和奖金金额之间的差额 (difference_sum)
2. 然后我需要获取 payment.paid_sum ( payments_sum ) 的总和
3. 然后我需要按条件排序 if ( payment_sum - Difference_sum <= 0)

所以我做了这个查询，但它说 Difference_sum 不存在:

SELECT
    (contracts.legal_sum - contracts.bonus_sum) as difference_sum
    sum(payments.paid_sum) as payments_sum,
    CASE
        WHEN (difference_sum - payments_sum) <= 0 THEN "All paid"
        WHEN (difference_sum - payments_sum) > 0 THEN "Not paid"
    END AS isPaid
FROM contracts
INNER JOIN payments on contracts.id = payments.contract_id
WHERE isPaid = "All paid"
ORDER BY isPaid

示例输出:

+----------------+--------------+----------+
| difference_sum | payments_sum |  isPaid  |
+----------------+--------------+----------+
|             30 |           30 | All paid |
|             48 |           15 | Not paid |
|            100 |          100 | All paid |
+----------------+--------------+----------+

Answer 1

尝试

SELECT difference_sum, payments_sum, isPaid 
FROM (
    SELECT  payments_sum,
        (contracts.legal_sum - contracts.bonus_sum) as difference_sum
        CASE
            WHEN (contracts.legal_sum - contracts.bonus_sum - payments_sum) <= 0 THEN "All paid"
            WHEN (contracts.legal_sum - contracts.bonus_sum - payments_sum) > 0 THEN "Not paid"
        END AS isPaid
    FROM contracts
    INNER JOIN ( 
       SELECT contract_id, sum(payments.paid_sum) as payments_sum
       FROM payments 
       GROUP BY contract_id
    ) payments on contracts.id = payments.contract_id
) q
WHERE isPaid = "All paid"
-- why order by a single value ?
-- ORDER BY isPaid 

可能您还需要添加contract_id列以使其更有用。