php - 生成按天和部门 mysql 分组的报告

Question

我有下表。

---+---------------+-------------+--------+
id | department_id | created     | amount |
---+---------------+-------------+--------+
1  |             0 | 2016-05-05  |    500 |
2  |             2 | 2016-05-05  |    100 |
3  |             1 | 2016-05-05  |    160 |
4  |             2 | 2016-05-05  |    260 |
5  |             1 | 2016-05-04  |    100 |
6  |             0 | 2016-05-04  |    150 |
7  |             1 | 2016-05-04  |    160 |
8  |             2 | 2016-05-04  |    160 |
---+---------------+-------------+--------+

我想生成报告以显示特定部门特定日期的收集量，需要如下表所示:

Date       |  0    |     1   |   2    |
2016-05-05 |  500  |  160    |  360   |
2016-05-04 |  150  |  260    |  160   |

我正在使用 cakephp。如果我使用 GROUP BY created 它有效，如果我使用 GROUP BY department_id 它有效。但是我需要 GROUP BY 来生成所需的报告。

我还尝试使用 this 生成从日期到日期的数组.然后尝试使用以下 CakePHP 查询运行在每个日期创建的 GROUP BY:

 //$ar = date array
    foreach ($ar as $k) {   
       $this->Transaction->find('all',array('conditions'=>array('Transaction.created'=>$k),'fields'=>array('SUM(Transaction.amount) AS s','Transaction.department_id'),'group'=>array('Transaction.department_id')));
    }

但如果日期范围很长，这对我来说似乎是一个缓慢的解决方案。有没有什么最好的方法可以在单个查询中完成。

请帮我解决这个问题。

Answer 1

这个问题的变体被无休止地提出和回答。一个典型的解决方案是这样的......

SELECT created date
     , SUM(CASE WHEN department_id = 0 THEN amount END) dept0
     , SUM(CASE WHEN department_id = 1 THEN amount END) dept1
     , SUM(CASE WHEN department_id = 2 THEN amount END) dept2
  FROM my_table
 GROUP
    BY date;