我早些時候問了一個關于如何執行此查詢的問題并收到了很好的回復。不幸的是，它比我通常運行的查詢要高級一些，所以我不知道如何檢索結果并將它們顯示在我的網頁上。這是原始問題原始問題的鏈接 <?php// Associative array$result = $db->query("SELECT Name, COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan, COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb, COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar, COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr, COALESCE(AVG(CASE WHEN mth = 5 THEN PTS END), 0) AS May, COALESCE(AVG(CASE WHEN mth = 6 THEN PTS END), 0) AS Jun, COALESCE(AVG(CASE WHEN mth = 7 THEN PTS END), 0) AS Jul, COALESCE(AVG(CASE WHEN mth = 8 THEN PTS END), 0) AS Aug, COALESCE(AVG(CASE WHEN mth = 9 THEN PTS END), 0) AS Sep, COALESCE(AVG(CASE WHEN mth = 10 THEN PTS END), 0) AS Oct, COALESCE(AVG(CASE WHEN mth = 11 THEN PTS END), 0) AS Nov, COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS Dec, AVG(PTS) AS AVGFROM ( SELECT Name, `Points Pass` AS PTS, MONTH(STR_TO_DATE(`OS Date`, '%a %b %e %H:%i:%s %Y')) AS mth FROM data) dGROUP BY Name");while($row = mysqli_fetch_array($result)) { ?> <tr </tr> <?php }if (!$result) { echo $db->error; }
1 回答
慕少森
TA貢獻2019條經驗 獲得超9個贊
你有幾個問題：
索引
$row需要用引號括起來，$row['name']否則你會收到很多“未定義常量”警告；$row['Jan']是檢索月度數據的正確方法，您需要為其他每個月復制它；替換
$avgGrade為$row['AVG']我不確定你想用什么來實現
<td class = "gScore-<?php echo $row[Jan]; ?>"></td>
你真的想要
<td class = "gScore"><?php echo $row[Jan]; ?></td>
您需要將
-- repeat for May to November行添加到查詢即
$result = $db->query("SELECT name,
COALESCE(AVG(CASE WHEN mth = 1 THEN PTS END), 0) AS Jan,
COALESCE(AVG(CASE WHEN mth = 2 THEN PTS END), 0) AS Feb,
COALESCE(AVG(CASE WHEN mth = 3 THEN PTS END), 0) AS Mar,
COALESCE(AVG(CASE WHEN mth = 4 THEN PTS END), 0) AS Apr,
COALESCE(AVG(CASE WHEN mth = 5 THEN PTS END), 0) AS May,
COALESCE(AVG(CASE WHEN mth = 6 THEN PTS END), 0) AS Jun,
COALESCE(AVG(CASE WHEN mth = 7 THEN PTS END), 0) AS Jul,
COALESCE(AVG(CASE WHEN mth = 8 THEN PTS END), 0) AS Aug,
COALESCE(AVG(CASE WHEN mth = 9 THEN PTS END), 0) AS Sep,
COALESCE(AVG(CASE WHEN mth = 10 THEN PTS END), 0) AS Oct,
COALESCE(AVG(CASE WHEN mth = 11 THEN PTS END), 0) AS Nov,
COALESCE(AVG(CASE WHEN mth = 12 THEN PTS END), 0) AS `Dec`,
AVG(PTS) AS AVG
FROM (
SELECT name, PTS AS PTS, MONTH(STR_TO_DATE(DATE, '%a %b %e %H:%i:%s %Y')) AS mth
FROM data
) d
GROUP BY name");
- 1 回答
- 0 關注
- 91 瀏覽
添加回答
舉報
0/150
提交
取消