如何限定CASE表达式中的条件（类似于WHERE子句）

Here is the table I have

Company  |  Year |  Amount
------------------------
CompanyA | 2008  |   5
CompanyA | 2008  |   4
CompanyB | 2008  |   4
CompanyB | 2009  |   1
CompanyC | 2009  |   4
CompanyC | 2010  |   2

Pseudo Code

SELECT company,
CASE WHEN year = 2008 THEN select max(amount) for each company where the year is = 2008
CASE WHEN year = 2009 THEN select max(amount) for each company where the year is = 2009
GROUP BY company

I've been able to get the CASE to work but it's selecting the MAX(amount) of all years and grouping by company, but I need the max per year.

我已经能够使CASE工作,但是它选择了所有年份的MAX(金额)并按公司分组,但我需要每年最大值。

I think I need the condition in the CASE expression to be qualified with a WHERE clause or perhaps a nested CASE but can't get it to work.

我认为我需要CASE表达式中的条件使用WHERE子句或嵌套CASE进行限定,但无法使其工作。

Real Code

select
record_id AS record_id,
invoice_date AS invoice_date,
company_id AS company_id,
company_name AS company_name,
fiscal_year AS fiscal_year,
(CASE fiscal_year WHEN '2008' THEN MAX(amount) ELSE 0 END) AS `2008`,
(CASE fiscal_year WHEN '2009' THEN MAX(amoutn) ELSE 0 END) AS `2009`,
(CASE fiscal_year WHEN '2010' THEN MAX(amount) ELSE 0 END) AS `2010`,
(CASE fiscal_year WHEN '2011' THEN MAX(amount) ELSE 0 END) AS `2011`,
(CASE fiscal_year WHEN '2012' THEN MAX(amount) ELSE 0 END) AS `2012`,
(CASE fiscal_year WHEN '2013' THEN MAX(amount) ELSE 0 END) AS `2013`
from tbl
group by company_name
order by invoice_date desc, record_id desc;

Any help would be mucho appreciated! Thx

任何帮助都会非常感激!谢谢

2 个解决方案

#1

Try this instead:

试试这个:

SELECT company,
  MAX(CASE WHEN year = 2008 THEN amount ELSE 0 END) AS '2008',
  MAX(CASE WHEN year = 2009 THEN amount ELSE 0 END) AS '2009'
FROM tbl
GROUP BY company;

See it in action:

看到它在行动:

SQL Fiddle Demo

SQL小提琴演示

This will give you:

这会给你:

|  COMPANY | 2008 | 2009 |
--------------------------
| CompanyA |    5 |    0 |
| CompanyB |    4 |    1 |
| CompanyC |    0 |    4 |

#2

This is how you pivot a table in MySQL:

这是你在MySQL中转动表格的方法:

select max(case fiscal_year when '2008' then amount else 0 end) as '2008',
       max(case fiscal_year when '2009' then amount else 0 end) as '2009',
       ...

#1