mysql - 我如何将带有 case 语句的 mysql 数据提取到单独的列中

Question

我的表格如下:

sales: customerid, invoice, saledate, amount
journal: reference, date, type, amount
customers: customerid, customername

我需要从此表中提取数据并以以下格式显示:

customer name  | jan sales| feb sales| march sales| april sales|

等等

我正在尝试以下查询:-

     SELECT
    customers.customername AS customer_name,
    CASE WHEN EXTRACT(MONTH FROM journal.date) = 1 THEN SUM(journal.amount) AS january_sales ELSE 0 END,
    CASE WHEN EXTRACT(MONTH FROM journal.date) = 1 THEN SUM(journal.amount) AS feb_sales ELSE 0 END,
    CASE WHEN EXTRACT(MONTH FROM journal.date) = 1 THEN SUM(journal.amount) AS march_sales ELSE 0 END
        FROM customers
        JOIN sales ON sales.customerid = customers.customerid
        JOIN journal ON journal.reference = sales.invoice
        WHERE
        journal.type = 'sale' AND
        journal.date BETWEEN '2013-01-01' AND '2013-03-31' GROUP BY
        customers.customername

我无法让这个查询工作。如有任何帮助，我们将不胜感激。

Answer 1

在SUM中使用CASE

SELECT 
  customers.customername AS customer_name,
  SUM(
    CASE
      WHEN EXTRACT(MONTH FROM journal.date) = 1 
      THEN journal.amount 
      ELSE 0 
    END
  ) AS january_sales,
  SUM(
    CASE
      WHEN EXTRACT(MONTH FROM journal.date) = 1 
      THEN journal.amount 
      ELSE 0 
    END
  ) AS feb_sales,
  SUM(
    CASE
      WHEN EXTRACT(MONTH FROM journal.date) = 1 
      THEN journal.amount 
      ELSE 0 
    END
  ) AS march_sales 
FROM
  customers 
  JOIN sales 
    ON sales.customerid = customers.customerid 
  JOIN journal 
    ON journal.reference = sales.invoice 
WHERE journal.type = 'sale' 
  AND journal.date BETWEEN '2013-01-01' 
  AND '2013-03-31' 
GROUP BY customers.customername