数据透视表 PHP/MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10649419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot Tables PHP/MySQL
提问by franglais
What's the best way of handling pivot tables in php/MySQL (or something to that effect)
在 php/MySQL 中处理数据透视表的最佳方法是什么（或类似的东西）
I have a query that returns information as below
我有一个查询返回如下信息
id eng week type sourceid userid
95304 AD 2012-01-02 Technical 744180 271332
95308 AD 2012-01-02 Non-Technical 744180 280198
96492 AD 2012-01-23 Non-Technical 1056672 283843
97998 AD 2012-01-09 Technical 1056672 284264
99608 AD 2012-01-16 Technical 1056672 283842
99680 AD 2012-01-02 Technical 1056672 284264
100781 AD 2012-01-23 Non-Technical 744180 280671
And I am wanting to build a report in PHP that counts by groups with column headers of week commencing. E.g.
我想在 PHP 中构建一个报告，该报告按组开始计数，列标题从一周开始。例如
week commencing: 2012-01-02 2012-01-09 2012-01-16 2012-01-23 2012-01-30
Total: 3 1 1 1 0
Technical: 2 1 1 0 0
Non-Technical: 1 0 0 1 0
But am not really sure where to start as the headers are dynamic depending on which month the report will be run for.
但我不确定从哪里开始，因为标题是动态的，具体取决于报告运行的月份。
I know how to pass the details of the month and retrieve all the data in PHP, but it's currently outputting in one column rather than being able to group and put it in an array.
我知道如何传递月份的详细信息并在 PHP 中检索所有数据，但它目前输出在一列中，而不是能够将其分组并将其放入数组中。
Any help appreciated!
任何帮助表示赞赏！
回答by Paul Bain
You can likely do this with a sub-query and then produce and aggregation of this data. Try something along the lines of this:
您可以使用子查询执行此操作，然后生成和聚合此数据。尝试一些类似的东西：
select week,
count(*) as total,
sum(technical) as technical,
sum(non_technical) as non_technical)
from(
select week,
case(type) when 'Technical' then 1 else 0 END as technical,
case(type) when 'Non-Technical' then 1 else 0 END as non_technical
) as data
GROUP BY week
