php - 将具有多个值的水平表转换为聚合垂直表

Question

我正在苦思冥想如何做到这一点，我的大脑被难住了。首先这是 mysql 和 php。

我有一个有 8 列的表格

id - user -a1 -a2 -a3 -a4 -a5 -a6
518   96   0   1   2   1   0   0
519  108   0   0   1   1   2   1
520   56   1   0   1   0   1   2

0 = no
1 = yes
2 = n/a

我希望能够选择其中的 6 列，但我想这样显示:

Question - Yes                     - No      - N/A
   a1       %of all rows with 1     %of 0    %of 2
   a2       %of all rows with 1     %of 0    %of 2
   a3       %of all rows with 1     %of 0    %of 2
   a4       %of all rows with 1     %of 0    %of 2 
   a5       %of all rows with 1     %of 0    %of 2
   a6       %of all rows with 1     %of 0    %of 2

对于 % 我想我会做这样的事情:

$i = 0;
if ($thequestion->a1==1) $i ++;
$num_amount = $i;
$num_total = '25';

$yespercentage = round($num_amount * 100 / $num_total)."%";

任何正确方向的帮助/指示将不胜感激。

Answer 1

您可以使用 GROUP BY 和 aggregate functions 对单个问题执行此操作.然后，您可以使用 UNION 将 6 个查询(针对六个问题)组合成一个结果。通过使用 SUM(CASE WHEN ...)，您可以分别计算三种情况:yes、no、n/a。

有点长，但我不知道比这更好的了。它应该准确输出您需要的表格，没有百分比。随意插入百分比计算，也可以在此 SQL 中完成:

SELECT 
  "a1" AS question,
  SUM(CASE WHEN a1 = 0 THEN 1 ELSE 0 END) AS no,
  SUM(CASE WHEN a1 = 1 THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN a1 = 2 THEN 1 ELSE 0 END) AS na,
  COUNT(id) AS total
FROM tbl 
UNION ALL
SELECT 
  "a2" AS question,
  SUM(CASE WHEN a2 = 0 THEN 1 ELSE 0 END) AS no,
  SUM(CASE WHEN a2 = 1 THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN a2 = 2 THEN 1 ELSE 0 END) AS na,
  COUNT(id) AS total
FROM tbl 
UNION ALL
SELECT 
  "a3" AS question,
  SUM(CASE WHEN a3 = 0 THEN 1 ELSE 0 END) AS no,
  SUM(CASE WHEN a3 = 1 THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN a3 = 2 THEN 1 ELSE 0 END) AS na,
  COUNT(id) AS total
FROM tbl 
UNION ALL
SELECT 
  "a4" AS question,
  SUM(CASE WHEN a4 = 0 THEN 1 ELSE 0 END) AS no,
  SUM(CASE WHEN a4 = 1 THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN a4 = 2 THEN 1 ELSE 0 END) AS na,
  COUNT(id) AS total
FROM tbl 
UNION ALL
SELECT 
  "a5" AS question,
  SUM(CASE WHEN a5 = 0 THEN 1 ELSE 0 END) AS no,
  SUM(CASE WHEN a5 = 1 THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN a5 = 2 THEN 1 ELSE 0 END) AS na,
  COUNT(id) AS total
FROM tbl 
UNION ALL
SELECT 
  "a6" AS question,
  SUM(CASE WHEN a6 = 0 THEN 1 ELSE 0 END) AS no,
  SUM(CASE WHEN a6 = 1 THEN 1 ELSE 0 END) AS yes,
  SUM(CASE WHEN a6 = 2 THEN 1 ELSE 0 END) AS na,
  COUNT(id) AS total
FROM tbl 

示例百分比:

SELECT
  "a1" AS question,
  (SUM(CASE WHEN a1 = 0 THEN 1 ELSE 0 END) / COUNT(id) * 100) AS no_percentage,
  (SUM(CASE WHEN a1 = 1 THEN 1 ELSE 0 END) / COUNT(id) * 100) AS yes_percentage,
  (SUM(CASE WHEN a1 = 2 THEN 1 ELSE 0 END) / COUNT(id) * 100) AS na_percentage
FROM tbl

所需的 PHP 打印示例:

<table>
  <thead>
    <tr>
      <td>Question</td>
      <td>Yes %</td>
      <td>No %</td>
      <td>N/A %</td>
    </tr>
  </thead>
  <tbody>
<?php
// given this functions returns the result set as multi-dimensional array
$rows = get_records_sql($thequery);
foreach ($rows as $row) {
    echo '<tr>';
    echo '<td>'.$row->yes_percentage.'%</td>';
    echo '<td>'.$row->yes_percentage.'% </td>';
    echo '<td>'.$row->no_percentage.'% </td>';
    echo '<td>'.$row->na_percentage.'% </td>';
    echo '</tr>';
}
?>
  </tbody>
</table>