数据透视表PHP/MySQL [英] Pivot Tables PHP/MySQL

问题描述

在php/MySQL中处理枢纽分析表的最佳方法是什么(或达到这种效果的方法)

What's the best way of handling pivot tables in php/MySQL (or something to that effect)

我有一个查询，返回的信息如下

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

我想用PHP构建一个报告，该报告按每周开始的列标题按组进行计数.例如

And I am wanting to build a report in PHP that counts by groups with column headers of week commencing. E.g.

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.

我知道如何传递月份的详细信息并检索PHP中的所有数据，但是当前它只输出到一列中，而不是能够分组并放入数组中.

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.

任何帮助表示赞赏！

推荐答案

您可能可以通过子查询来执行此操作，然后生成并汇总此数据.尝试以下方法:

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

这篇关于数据透视表PHP/MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！