对MYSQL来说很新,但是我如何将这两个查询组合起来显示类型为'I'的SUM(total)减去类型为'p'的SUM(total)。

SELECT userid, SUM(total)
FROM invoices
WHERE type = 'i'
GROUP BY 1

SELECT userid, SUM(total)
FROM invoices
WHERE type = 'p'
GROUP BY 1

最佳答案

你可以使用case表达式

SELECT userid,
    SUM(case when type = 'i' then total else 0 end) typei,
    SUM(case when type = 'p' then total else 0 end) typep,
    SUM(case when type = 'i' then total else 0 end) - SUM(case when type = 'p' then total else 0 end) as diff
FROM invoices
GROUP BY userid

或者你可以把它写成
SUM(case when type = 'i' then total when type = 'p' then -1 * total else 0 end) as diff

编辑:要筛选聚合产生的结果,可以使用having子句
SELECT userid,
    SUM(case when type = 'i' then total else 0 end) typei,
    SUM(case when type = 'p' then total else 0 end) typep,
    SUM(case when type = 'i' then total else 0 end) - SUM(case when type = 'p' then total else 0 end) as diff
FROM invoices
GROUP BY userid
HAVING diff > 0

10-07 19:09
查看更多