mysql求和一张表但类型不同

Question

task_payments

SELECT t.id AS task_id, t.name, t.created_at
       ,COALESCE(SUM(tp1.amount),0) AS paid
       ,COALESCE(SUM(tp2.amount),0) AS paid_back
       FROM tasks AS t
       LEFT JOIN task_payments AS tp1 ON tp1.task_id=t.id AND tp1.type='1'
       LEFT JOIN task_payments AS tp2 ON tp2.task_id=t.id AND tp2.type='0'
       WHERE t.customer_id='4'
       GROUP BY tp1.task_id, tp2.task_id
       ORDER BY t.id ASC

您好，task_payments 有两种类型(1 或 0)。类型 0 被偿还。类型 1 已支付。结果我想要单独的总金额。所以我想要结果；task_id=5付费=450paid_back=10我应该使用加入。如果有过滤请求，我将在 where 子句上使用 paid 和 paid_colums。例如:和 paid_back>0

Answer 1

也许以下查询可以帮助您 :D

SELECT  x.*
FROM
    (
        SELECT  a.task_id,
                SUM(CASE WHEN b.type = 1 THEN b.amount ELSE 0 END) paid,
                SUM(CASE WHEN b.type = 0 THEN b.amount ELSE 0 END) paidBack
        FROM    tasks a
                LEFT JOIN task_payments b
                    ON a.id = b.task_id
        -- WHERE a.customer_id = 4
        GROUP BY a.task_id
    ) x
-- WHERE    x.paid > 100   -- sample Request