对于每个外科中心,我都构建了MySQL SUM查询,总结了执行的外科手术数量。在 PHP 中,我将结果显示在一个包含两列的表中。
现在,我想计算所有中心的 SUM 的总和并同样显示它们。
问题在于,相同的 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 显示一个接一个的手术,由回车符分隔
但是,我期望的是一个包含两列的表格,其中左侧给出了操作名称,右侧列出了手术次数。
对不起,但我太愚蠢了,没有意识到这一点。
请问有人为我提供解决方案吗?
Union 仅组合不相同的行。因此,就您的目的而言,工会并非在所有情况下都是正确的选择。
所以有了这张表
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
但是你的原版似乎你想要两行,所以....