sql - 在一个查询中结合 SQL Select 和 AVG
问题描述
我尝试将 aSELECT
和AVG
query 合二为一。我的查询正在运行,但AVG
结果未按预期显示。错误是:
未定义的索引
我以为我可以结合AVG
并SELECT
喜欢这样?
结果将是一个列表,还应显示平均进度值。如果我将这个 AVG 查询单独放在“fetch”中,它可以工作,但只显示第一个结果。所以这对我来说不是一个解决方案,因为我会有更多的行。
我希望有人可以帮助我重建这个奇迹:)
<?php
$statement = $pdo->prepare("
SELECT
audit.id as audit_id,
audit.uid,
audit.assigned_auditor,
audit.audit_req_comment,
audit.audit_req_date,
audit.audit_date_start,
audit.general_audit_status,
audit.audit_request_date,
audit.audit_type,
audit.audit_date_start,
questionaire.quest_name,
users.nachname,
suppliers.supplier_name,
suppliers.supplier_city,
suppliers.supplier_country,
(SELECT AVG(progress) AS progress FROM answers WHERE relevant = '1' AND audit_id = :audit_id AND rating != required_answer)
FROM audit
JOIN users ON audit.uid = users.id
JOIN suppliers ON audit.supplier_id = suppliers.id
JOIN questionaire ON audit.questionaire_id = questionaire.id
WHERE
audit.cid = :cid
AND audit.general_audit_status = 'Maßnahmenplan'
AND audit.assigned_auditor = :assigned_auditor"
);
$result = $statement->execute(array(':cid' => $cid, ':assigned_auditor' => $user['id'], ':audit_id' => 4));
$count = 1;
while ($row = $statement->fetch()) {
// Datum umwandeln
$original_date = $row['audit_date_start'];
// Creating timestamp from given date
$timestamp = strtotime($original_date);
// Creating new date format from that timestamp
$new_date = date("d.m.Y", $timestamp);
// Audit Typ Namensgebung
if ($row['audit_type'] == "AR") {
$audit_type = "Externes Audit";
}
if ($row['audit_type'] == "RA") {
$audit_type = "Remote Audit";
}
if ($row['audit_type'] == "IA") {
$audit_type = "Internes Audit";
}
if ($row['audit_type'] == "SAA") {
$audit_type = "Self Assessment Audit";
}
//Berechne die durchschnittliche Abarbeitung aller Maßnahmen dieses Audits
//$statement = $pdo->prepare("SELECT AVG(progress) AS progress FROM answers WHERE relevant = '1' AND audit_id = :audit_id AND rating != required_answer");
//$statement->execute(array(':audit_id' => $row['audit_id']));
//$total_progress = $statement->fetch();
echo "<tr>";
echo "<td>" . $row['audit_id'] . "</td>";
echo "<td>" . $new_date . "</td>";
echo "<td>" . $row['supplier_name'] . "</td>";
echo "<td>" . $row['supplier_city'] . " (" . $row['supplier_country'] . ")</td>";
echo "<td>" . $row['quest_name'] . "</td>";
echo "<td>" . $audit_type . "</td>";
echo "<td>" . round($row['progress'], 0) . " %</td>";
echo '<td align="center"><a href="audit_edit.php?id=' . $row['audit_id'] . '"><i class="fas fa-edit"></i></a></td>';
echo "</tr>";
}
?>
解决方案
据我了解您的问题,您只需要在外部查询(而不是内部查询本身)中对子查询的结果进行别名:
SELECT
audit.id as audit_id,
audit.uid,
audit.assigned_auditor,
audit.audit_req_comment,
audit.audit_req_date,
audit.audit_date_start,
audit.general_audit_status,
audit.audit_request_date,
audit.audit_type,
audit.audit_date_start,
questionaire.quest_name,
users.nachname,
suppliers.supplier_name,
suppliers.supplier_city,
suppliers.supplier_country,
(
SELECT AVG(progress)
FROM answers
WHERE relevant = 1 AND audit_id = :audit_id AND rating != required_answer
) AS progress --> here
FROM audit
JOIN users ON audit.uid = users.id
JOIN suppliers ON audit.supplier_id = suppliers.id
JOIN questionaire ON audit.questionaire_id = questionaire.id
WHERE
audit.cid = :cid
AND audit.general_audit_status = 'Maßnahmenplan'
AND audit.assigned_auditor = :assigned_auditor"
推荐阅读
- google-app-engine - 无法使用使用外部库的运行时 1.12 部署 AppEngine Go 应用
- bootstrap-4 - Boostrap 4 崩溃:标题和徽章不在同一行
- c# - 在 Lucene.net 中,我想在同一方面类别中执行 AND,但似乎没有简单的方法可以做到这一点
- python - How to modify contents of a list in python
- angular - 类型“AngularFireAuthModule”错误中不存在属性“auth”
- java - Java SPI - 选择单个实现
- node.js - 您可以在不渲染的情况下更新 ejs 中的值吗?
- c# - Entity Framework Core 中的 IQueryable 设置实体属性等于其他实体(表)上的行数
- java - 如何使用 Firebase Cloud Messaging 在前台显示多个通知
- asp.net - 是什么导致 ASP.NET MVC 应用程序中的 POST 方法出现 405 错误?