对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