php - 在 MySQL 中通过 UNION 连接求和并在 PHP 中使用 ECHO 显示

Question

对于每个外科中心，我都构建了 MySQL SUM 查询，总结了所执行的外科手术的数量。在 PHP 中，我将结果显示在包含两列的表格中。

现在，我想计算所有中心的总和并以同样的方式显示它们。

问题在于，相同的 echo 命令确实显示了彼此下方的外科手术类型，但仅显示了 SUM over SUM 结果的第一个(参见 3。)

PHP函数代码和SQL查询代码如下所示:

function getGlobalAlleOPVerfahrenStratification() {
  global $oDatabase;
  $qQuery = ' 
  SELECT SUM(X1.Surgery1) AS "Surgery1" FROM (
    SELECT SUM(op.OPVerfahren = "0") AS "Surgery1"
    FROM operation op
    UNION
    SELECT SUM(op.OPVerfahren = "1") AS "Surgery1"
    FROM operation op
    UNION
    SELECT SUM(op.OPVerfahren = "20") AS "Surgery1"
    FROM operation op
  ) X1
  UNION
  SELECT SUM(X2.Surgery2) AS "Surgery2" FROM (
    SELECT SUM(op.OPVerfahren = "0") AS "Surgery2"
    FROM operation op
    UNION
    SELECT SUM(op.OPVerfahren = "1") AS "Surgery2"
    FROM operation op
    UNION
    SELECT SUM(op.OPVerfahren = "20") AS "Surgery2"
    FROM operation op
  ) X2; ';

  $rQuery = mysql_query($qQuery, $oDatabase);
  $result = array("count" => mysql_num_rows($rQuery), "result" => $rQuery);
  return $result;
}


function writeGlobalOP1OPVerfahrenStratification($rQuery) {
  if (!$rQuery) {
    $message = '<br>';
    $message .= '<b>Ungültige Abfrage:</b> ' . mysql_error() . '<br>';
    $message .= '<b>Gesamte Abfrage:</b> ' . $qQuery . '<br>';
    echo $message;
  }

  if (mysql_num_rows($rQuery) == 0) {
    $message = '<br>';
    $message .= '<p class="error">Zu diesem Zeitraum liegen noch keine Daten vor.</p> ' . mysql_error() . '<br>';
    echo $message;
  }

  //echo '<table>';
  echo '<tr>';
  echo '<th style="font-size: 30px; border-left: 6px solid blue;">';
  echo "Alle Zentren";
  echo '</th>';
  echo '<th class="data_table_r" style="font-size: 30px;">';
  echo "Summen";
  echo '</th>';
  echo '</tr>';

  while ($row = mysql_fetch_assoc($rQuery)) {
    echo '<tr>';
    echo '<td style="font-size: 30px; border-left: 6px solid blue;">';
    echo "Operation 1";
    echo '</td>';
    echo '<td class="data_table_r" style="font-size: 30px;">';
    echo $row['Surgery1'];
    echo '</td>';
    echo '</tr>';
    echo '<tr>';
    echo '<td style="font-size: 30px; border-left: 6px solid blue;">';
    echo "Operation 2";
    echo '</td>';
    echo '<td class="data_table_r" style="font-size: 30px;">';
    echo $row['Surgery2'];
    echo '</table>';
    }
}

而不是所有

echo ...

在 while 语句之后执行命令，只需编写

echo $row['Surgery1'];
echo '<br />';

PHP 依次显示手术，并用回车符分隔

但是，我期望的是一个有两列的表格，左侧给出手术名称，右侧列出手术数量。

抱歉，我太笨了，没有意识到这一点。

请问有人可以帮我解决吗？

Answer 1

并集仅合并不相同的行。因此，就您的目的而言，工会并非在所有情况下都是正确的选择。

这张表就是这样

CREATE TABLE operation
(`id` int, `OPVerfahren` varchar(10))
;

INSERT INTO operation
(`id`, `OPVerfahren`)
VALUES
(1, '0'),
(2, '1'),
(4, '0'),
(5, '20'),
(6, '0'),
(7, '1'),
(8, '20'),
(9, '0'),
(10, '0')
;

还有这个选择语句

    SELECT "Surgery1",SUM(IF (op.OPVerfahren = "0",1,
               if( op.OPVerfahren = "1",1,
                if (op.OPVerfahren = "20", 1,0)
                 )
              )
          ) "Surgery1"
  FROM operation op
  union
  SELECT "Surgery2", SUM(IF (op.OPVerfahren = "0",1,
               if( op.OPVerfahren = "1",1,
                if (op.OPVerfahren = "20", 1,0)
                 )
              )
          ) "Surgery2"
FROM operation op

你得到以下结果

Surgery1    Surgery1
Surgery1    9
Surgery2    9

这是本示例的正确结果。当然，您可以让它适应您的实际结构和数据

我个人会做以下事情

    SELECT SUM(IF (op.OPVerfahren = "0",1,
               if( op.OPVerfahren = "1",1,
                if (op.OPVerfahren = "20", 1,0)
                 )
              )
          ) "Surgery1",
          SUM(IF (op.OPVerfahren = "0",1,
               if( op.OPVerfahren = "1",1,
                if (op.OPVerfahren = "20", 1,0)
                 )
              )
          ) "Surgery2"
FROM operation op

哪个会吸引你

Surgery1    Surgery2
9           9

但是你原来的似乎你想要两行，所以......