将Group By结果行转换为列(PHP MYSQL)

Question

我有桌子。

Country Status
USA     A
USA     A
USA     B
USA     C
UK      A
UK      D
UK      D
China   A
China   A
China   C
China   C

我想写一个查询，并在表格中显示结果，如下所示。

Country A   B   C   D   Total
USA     2   1   1   0   4
UK      1   0   0   2   3
China   2   0   2   0   4
Total   5   1   3   2  11

$q="SELECT Country,Status,Count(Status) as Stat Group BY Country,Status";
$r=mysql_query($q);
while($o=mysql_fetch_object($r)){
$t .="<tr>
      <td>$o->Country</td>
      <td>$o->Status</td>
      <td>$o->Stas</td>
      </tr>";
}

它会输出。就像上面我想要的。它对国家和状态进行分组，但我不想多次显示国家。我想要将Status的行输出显示为column，并显示它的计数。

USA   A   2
USA   B   1
USA   C   1
UK    A   1
UK    D   2
China A   2
China C   2

Answer 1

您可以通过查询或直接进入php代码来完成此操作。在查询中，它被称为PIVOTing表。

它将是：

 select Country, A, B, C, D, (A+B+C+D) Total
   from (
     select Country
            sum(case status = 'A' then 1 else 0 end) as A,
            sum(case status = 'B' then 1 else 0 end) as B,
            sum(case status = 'C' then 1 else 0 end) as C,
            sum(case status = 'D' then 1 else 0 end) as D
       from yourtable
     group by Country
    ) as t

这样做的问题是，对于每个新的status，您必须更改您的查询，添加一个新的sum。

Answer 2

我也有同样的需求，下面是我得到的sql结果：

SELECT practiceCode, A, B, C, D, (A+B+C+D) Total
FROM (
   SELECT practiceCode,
        SUM(CASE WHEN TYPE = 1 THEN 1 ELSE 0 END) AS A,
        SUM(CASE WHEN TYPE = 2 THEN 1 ELSE 0 END) AS B,
        SUM(CASE WHEN TYPE = 3 THEN 1 ELSE 0 END) AS C,
        SUM(CASE WHEN TYPE = 4 THEN 1 ELSE 0 END) AS D
   FROM laborder
   GROUP BY practiceCode, TYPE
) AS t