mysql - MYSQL 的 CASE 问题

Question

我有一个表，用于存储买方和供应商之间的交易，交易按表示贷方票据或发票的“doctype”进行分类。我正在尝试创建一个 View 来显示相同​​交易的类似账本的 View 。

SELECT
SUM(CASE doctype WHEN doctype = 1 THEN docvalue END) AS Invoice, 
SUM(CASE doctype WHEN doctype = 2 THEN docvalue END) AS CreditNote, 
SUM(CASE doctype WHEN doctype = 3 THEN docvalue END) AS JournalEntry, 
SUM(CASE doctype WHEN doctype = 4 THEN docvalue END) AS Payment
FROM transactions 
group by buyer

当我运行此查询时，我看到第一个 CASE 语句的结果，但其余语句返回空值。有更好的方法吗？

我的目标是将此作为 View ，但我什至无法获取查询来显示我想要的内容。我还尝试了以下作为一个可怕的黑客，但它甚至不起作用

SELECT
(sum(docvalue)
    FROM transactions where doctype =1) as invoices,
(sum(docvalue)
    FROM transactions where doctype =2) as creditnotes,
(sum(docvalue)
    FROM transactions where doctype =3) as journals,
(sum(docvalue)
    FROM transactions where doctype =4) as payments
from transactions

我想把头发扯下来，但我秃了!

Answer 1

CASE 的语法是不正确的。这是修改后的版本:

SELECT
SUM(CASE doctype WHEN 1 THEN docvalue END) AS Invoice, 
SUM(CASE doctype WHEN 2 THEN docvalue END) AS CreditNote, 
SUM(CASE doctype WHEN 3 THEN docvalue END) AS JournalEntry, 
SUM(CASE doctype WHEN 4 THEN docvalue END) AS Payment
FROM transactions 
group by buyer